ROW TO COL

[kayarsenal]

Hi Sunshine,
This question is not about the previous one.I solved the previous one already. The NO is the hash file is newly created. I need that hash file so as to confirm if the orderNO(Y345 etc) is not in the hash file. I have only NO in the hash file. If the NO from the source is not present in the hashed file, then its loaded into the target file. If it would be present, it is rejected along with all orders by the customer.That is, if a customer ordered pepsi and cola, and pepsi is rejected, cola is subsequently rejected.

0000 cola 3
0000 pepsi 4

0001 Dell 3
0001 IBM 4
If cola were to be in the hash file, it would be rejected into a separate reject file,not only cola though, but also pepsi since the orders are from the same customer.Otherwise, its loaded into the target file.

[chulett]

Suggestion: When your problem is solved, the thread is closed. It only confuses things to immediately launch into a new problem in the same thread. It also makes it hard for people searching for solutions later.

Best Practice is to start a new thread with the new question / problem.

[Sunshine2323]

Hi kayarsenal ,

If I understand your question correctly then

You need to reject all the records that have the NO present in the hash file
And for this customer number in the rejected record, you need to reject all the remaining orders

Input
ID NO AMT
0000 Y345 7
0000 Y346 5
0001 Y322 4
0001 Y341 6
0002 Y345 6
0002 Y234 2

So your Target File will contain
0000 Y345 7
0000 Y346 5
0001 Y322 4
0001 Y341 6

and the reject file will contain
0002 Y345 6
0002 Y234 2

If this is the requirement, then the job design will be as follows:

<Step1>Creation of 2 Hash Files with one column
e.g HASH_ID with column ID as the Key
and HASH_NO with coulumn NO as the Key

<Step 2>
We are going to read and write to the same hash files as before

--------------- Hash File(HASH_ID) Hash File(HASH_NO)
------------------------------------|---------------|
Source File--------------TransformerforLookUp---------------------->2 outputs Target and Reject File
------------------------------------|---------------|
--------------- Hash File(HASH_ID) Hash File(HASH_NO)

Transformer Constraints for:
Hash File(HASH_ID) :Not(IsNull(DSLink11.NO))
Hash File(HASH_NO):
Target:(Isnull(DSLink10.ID) and IsNull(DSLink11.NO))
Reject File:Not(Isnull(DSLink10.ID)) or Not(IsNull(DSLink11.NO)

Transformer Derivation for:
Hash File(HASH_ID):Source.ID
Hash File(HASH_NO):Source.NO
Target:all the three columns from the source
Reject File:all the three columns from the source

The job will reject all the records if the NO is found in the hash file. It will capture the ID for such a record and reject all the records with that ID.

Hope this helps