ROW TO COL

[kayarsenal]

Hi DSperts,
I have an input file of three columns in the ffg format

ID ControlNO Amount
0000 Y345 7
0000 Y346 5

0001 Y322 4
0001 Y341 6

0002 Y345 6
0002 Y234 2

I want this transformed to have this format

since ID is repeated I dont repeat it in the next col. I want the target file to look like this
0000
Y345, 7
Y346, 5
0001
Y322, 4
Y341, 6
0002
Y345, 6
Y234, 2

THANKS

[kcbland]

A stage variable to track changes in the first column should do the trick. The only difficulty you will have is the extra output row when the change occurs.

You'll need to output a row with the group value, then output the remaining data for the first row in that group as a second output row. Then, for all remaining rows where the group value is the same, output just the remaining data. For this purpose, you will need two output links from a single transformer stage. The first link outputs the group value only on a group change (which the stage variable tracks), and the second link outputs just the data portion for every single source row.

The trick now is to collate the data from both output links. The easiest method is to output the @INROWNUM value to both output links as a sorting key, and a literal "1" for the group link and a literal "2" (or another stage variable to count the occurrences within that group and keep them in original order) for the data link as a second sort key. Make these the first two columns in the output link metadata. Send each output link to a different sequential text file, maybe pipe delimited.

Now, concatenate sort the two files together, using columns 1 and 2 as the sort key. Something like "sort -d"|" -k1,1n -k2,2n yourfiles*.dat > mergedfile.dat". Your differing metadata means that DS will struggle with non-consistent table definitions, so using the Link Collector and Sort stage is kind of tricky, but you could make that work. I like the trick I showed here.

Lastly, re-read the file and strip off the first two columns to get to your final requirement.

[kayarsenal]

HI, thanks for the reply. COuld u explain a bit codewise, so that I can understand better whta u mean. Im new to DS.

Thanks

[kayarsenal]

For the INROWNUM, it means that in each of the output file, I should add a new column for the INROWNUM?If Im right..In that case I would expect the first group 0000 to have one and then the respective cols 2 and 3 for this example. Whjat aboiut if the group increases to 2 in the ssecond row in the group? The idea I guess is to sort in order 1,2,3,4 to get the expected output. Thanks for ur time.
Your comment
"You'll need to output a row with the group value, then output the remaining data for the first row in that group as a second output row. Then, for all remaining rows where the group value is the same, output just the remaining data. For this purpose, you will need two output links from a single transformer stage. The first link outputs the group value only on a group change (which the stage variable tracks), and the second link outputs just the data portion for every single source row.

The trick now is to collate the data from both output links. The easiest method is to output the @INROWNUM value to both output links as a sorting key, and a literal "1" for the group link and a literal "2" (or another stage variable to count the occurrences within that group and keep them in original order) for the data link as a second sort key. Make these the first two columns in the output link metadata. Send each output link to a different sequential text file, maybe pipe delimited. "

Could u pls show me some simple code on this? That would make me understand faster. Thanks

Now, concatenate sort the two files together, using columns 1 and 2 as the sort key. Something like "sort -d"|" -k1,1n -k2,2n yourfiles*.dat > mergedfile.dat". Your differing metadata means that DS will struggle with non-consistent table definitions, so using the Link Collector and Sort stage is kind of tricky, but you could make that work. I like the trick I showed here.

Lastly, re-read the file and strip off the first two columns to get to your final requirement.

[kayarsenal]

how would the stage var look like?sorry for the questions...still new to it

[kcbland]

Use three stage variables:

Code: Select all

GroupKeyChanged = CurrentGroupKey <> inlink.groupkeycolumn
CurrentGroupInternalNumber = If GroupKeyChanged Then 1 Else CurrentGroupInternalNumber + 1
CurrentGroupKey = inlink.groupkeycolumn

In the output link for the group key, use a constraint of just stage variable GroupKeyChanged, which is a BOOLEAN true or false value. The second value of the sorting key, to maintain original group order, is the stage variable CurrentGroupInternalNumber.

[kayarsenal]

Thanks Kenny for your time. I will try your idea now.

Thanks

[ray.wurlod]

Oh my God, they've killed Kenny!

Ken or Kenneth, please - he doesn't like the other name.

[kcbland]

Thanks for backing me up Ray.

Ken, Kenneth, Mr. Bland, hey mister, buddy, yo, but for heavens sake, never Kenny.

[Sunshine2323]

Hi kayarsenal,

This is an alternate way of getting the output.

<Step1>

Create a hashfile(HASH_KEY) with a single column Col1

<Step2>

Read and write to the same hash file, doing a lookup on the key value in the hashfile with that in the source

Design for job:
--------------------------HashFile(HASH_KEY)
--------------------------|
Source File --- > Transformer -------> OutputFile
--------------------------|
--------------------------HashFile(HASH_KEY)

The same hash file is being read and written to.

Transformer Derivation for:

HashFile: KeyColumn from Source

OutputFile(Has only one column):
If (IsNull(Hash.Key1)) then Source.Key1 :char(10) : Source.Value1: "," : Source.Value2 else Source.Value1: "," : Source.Value2

Go the output file location and open it to check whether you have got the output in the desired format

In your case

Key-->ID
Value1-->ControlNO
Value2-->Amount


Hope this helps

[kayarsenal]

Hi Sunshine,
Perfect idea. It worked perfectly. Thanks a lot.
Kenny thanks too. Your idea also worked. I tried both.

[kayarsenal]

One more thing pls
ID NO AMT
0000 Y345 7
0000 Y346 5

0001 Y322 4
0001 Y341 6

0002 Y345 6
0002 Y234 2
Above is the input(d same) and I need to have this a database, but if for example Y345 is not a hashfile(NO), then the whole order by the customer is rejected, even Y346.
Thank u

[Sunshine2323]

Hi kayarsenal,

I cannot understand your recent post.

Also, the hash file has only field which is used as key and that will be the ID(0000,0001.....) in your case.
The lookup is only on the ID which will act as the key.

So please explain what you mean by

Above is the input(d same) and I need to have this a database, but if for example Y345 is not a hashfile(NO), then the whole order by the customer is rejected, even Y346.

[kayarsenal]

Hi Sunshine,
I have an input file
In the input file I have this:

ID NO AMT
0000 Y345 7
0000 Y346 5

0001 Y322 4
0001 Y341 6

0002 Y345 6
0002 Y234 2 etc

In the Hashfile I have this:
Y345
Y341
0002 etc
the order(for example 0000 Y345 7) is rejected if the NO(Y345) is in the HASHFILE. Not only is the ordere rejected, but all the orders by the customer that is Y346 as well. Since Customer 2 ordered Y345 as well, his orders are rejected. Now in the output file would be only CUSTOMER1 info.The problem I have is if Y345 is in the hash file,and the order is rejected, how I can reject all orders by the customer and send it to the reject file.

Thanks

[Sunshine2323]

Hi,

Are u reading the input correctly?

There is no need of the NO column to be present in the hash file.
Why are the values from the NO column present in your hash file?

Your Input File has 3 columns i.e.

ID NO AMT

So when u do view data on the souce stage you get 3 columns. Your delimiter for source file <assuming it is a text file> is 032

Now when u create the hash file you are creating the hash file with one column only.

You are using this hash file in your job for reading as well as writing.

Now when u are writing to this hash file you are just going to drag the first column (ID) from the source to hash file OUTPUT column derivation.

Only the values from the field ID should be inserted into your hash file
No values from NO column, Y345 should not be present in your hash file as the lookup is only against the first column and that is ID