column handling......

[nag0143]

hi,
i have a flat file in the following format.... has 2 columns..
sum alll
1234, 12
1234, 113
1234, 13
1234, 1415
1235 , 15
1235, 16
4, 17
4, 132
4, 234
4, 543
4, 236
234, 45
234, 45
234, 34

i want it to change to the following format..... making the first column as the key and group by..... making the columns into rows..... like..

1234, 12 113 13 1415
1235, 15 16
4, 17 132 234 543 236
234, 45 45 34

[kcbland]

Sort/group your data in the file by the group-by fields, then use stage variables to concatenate rows and output the concatenated row when the group-by fields break. You will have to add a trailing row to the source data to signal the end of the data file so that you can output the last row. This technique has been covered on the forum. If you can't find it, I'll check back later and help you.

[nag0143]

thank can u please....

[phanee_k]

Hi Nag,
Can you please let me know, if you are thru in ur logic.
Can you please provide me the solution, since i am also stuck up with this kind of logic.

[kcbland]

These two posts show some ways to use stage variables:


viewtopic.php?t=85229&highlight=stage+variables+kenneth

viewtopic.php?t=84848&highlight=stage+variables+kenneth

What you do is setup a stage variable to keep concatentating the values until there's a break, and output on a break. You will use a stage variable as the constraint expression. You can do it, just experiment, it will look easy after you figure it out.

[vdr123]

Can I use COLUMN EXPORT STAGE to do the flattenning part(vertical pivoting) of the data???

Can we use any of the other Restructure stages available in PX 7x?

Is there other way to do it without using the concatenation of fields suggested....

Please give you feedback ASAP....its URGENT!!!

[ray.wurlod]

See viewtopic.php?t=86415

[ray.wurlod]

Can I use COLUMN EXPORT STAGE to do the flattenning part(vertical pivoting) of the data???

Can we use any of the other Restructure stages available in PX 7x?

Is there other way to do it without using the concatenation of fields suggested....

Please give you feedback ASAP....its URGENT!!!

Rather than ask "can I...", why not try, and report your results?

How would you envisage using a column export stage? What other restructure stages did you have in mind? What approaches have you tried already?

There are at least three ways to design this task in DataStage. I still believe that the approach using stage variables, which has already been posted (Ken, the poster, has nearly 1000 posts on the Forum, and concomitant experience with the product), will be the most efficient.

[kcbland]

Can I use COLUMN EXPORT STAGE to do the flattenning part(vertical pivoting) of the data???

Can we use any of the other Restructure stages available in PX 7x?

Is there other way to do it without using the concatenation of fields suggested....

Please give you feedback ASAP....its URGENT!!!

But your post states you're on Windows. So why ask about PX?

[ray.wurlod]

vdr123 isn't the original poster.
vdr123's the one with the urgent PX requirement!

[vdr123]

Thanks Ray for pointing out that...

I have tried out the method suggested for this...i am able to get the results in a single row, BUT have a small problem of writting the output.

input file:
4.8,"1985-01-01",100871,3.2,"1985-12-31"
58,"1985-01-01",100890,46.4,""
54,"1984-06-01",100890,40.5,"1984-07-31"
35,"1986-06-01",100860,28,""
32,"1986-01-01",100860,25.6,"1986-05-31"
30,"1985-01-01",100860,24,"1985-12-31"
45,"1986-06-01",100861,36,""
42,"1986-01-01",100861,33.6,"1986-05-31"

output file: (field3, concatenation(field1))
100871,"4.8"
100890,"58"
100890,"5854"
100860,"35"
100860,"3532"
100860,"353230"
100861,"45"
100861,"4542"

How can I control the output from the transformer stage to the output...what type of contraint can i use to output(when it breaks)
I am using stage variables like: new, old, CODE.

Any feedback would be highly appreciated and helpfull.

[kcbland]

vdr123 isn't the original poster.
vdr123's the one with the urgent PX requirement!

Okay, shame on the second poster for stacking a different issue on this post. Start a new topic next time!

Sorry so crabby, home sick with the flu, wife and kids sick also.

[ray.wurlod]

Thanks Ray for pointing out that...

I have tried out the method suggested for this...i am able to get the results in a single row, BUT have a small problem of writting the output.

input file:
4.8,"1985-01-01",100871,3.2,"1985-12-31"
58,"1985-01-01",100890,46.4,""
54,"1984-06-01",100890,40.5,"1984-07-31"
35,"1986-06-01",100860,28,""
32,"1986-01-01",100860,25.6,"1986-05-31"
30,"1985-01-01",100860,24,"1985-12-31"
45,"1986-06-01",100861,36,""
42,"1986-01-01",100861,33.6,"1986-05-31"

output file: (field3, concatenation(field1))
100871,"4.8"
100890,"58"
100890,"5854"
100860,"35"
100860,"3532"
100860,"353230"
100861,"45"
100861,"4542"

How can I control the output from the transformer stage to the output...what type of contraint can i use to output(when it breaks)
I am using stage variables like: new, old, CODE.

Any feedback would be highly appreciated and helpfull.

If what you want in the output is input.field3 then input.field1, then this is exactly what you are seeing. Because you've declared field1 to be some kind of character string in the output, it is being quoted. You can change the quote character in the Format specifications for the Sequential File stage.

If what you want is a single column, with the value of input.field3 concatenated with the value of input.field1, then use the expression editor. First choose input.field3 from the operands, then choose concatenate from the operators, finally choose input.field1 from the operands. Voila! Your expression.

[vdr123]

Ray,
I want output file to look something like this..

output file: (field3, concatenation(field1))
100871,"4.8"
100890,"5854"
100860,"353230"
100861,"4542"

Presently I am getting:
output file: (field3, concatenation(field1))
100871,"4.8"
100890,"58"
100890,"5854"
100860,"35"
100860,"3532"
100860,"353230"
100861,"45"
100861,"4542"

[ray.wurlod]

Capture field3 into a stage variable, with a preceding stage variable to detect whether the value in field3 has changed.
Accumulate the concatenated string in yet another stage variable.
Constrain the Transfomer output to generate output only where the value of field3 changes (and @INROWNUM <> 1).
Make sure you process the final input line, perhaps by pre-generating a dummy last line in the input file.
All these techniques can be found by searching the Forum.