Page 1 of 1
How to achieve logic for horizontal pivot
Posted: Thu Aug 22, 2013 7:49 am
by skp
Hi,
My source is like below:
Col1 Col2
a b
c d
e f
g h
Output should be like below:
Column
a|c|e|g
b|d|f|h
Help me on this please.
Posted: Thu Aug 22, 2013 7:56 am
by asorrell
What exact release are you on? (functionality differs)
Are there any other columns in the record? If so, what should happen with them?
How many records are there? Some solutions might not work if you had millions to process.
Posted: Thu Aug 22, 2013 8:05 am
by skp
There are no other columns only two columns we will get from source but output should be one column but two different records 1 for first column and second one for second column
Posted: Thu Aug 22, 2013 8:07 am
by asorrell
Again, what revision are you on? How many records approximately?
Posted: Thu Aug 22, 2013 8:10 am
by skp
working on 9.1 version and there is no limit has been given by business for the number of records
Posted: Thu Aug 22, 2013 8:34 am
by asorrell
1) Setup two LongVarChar stage variables in a transformer, both initialized to empty string ("").
2) As each record comes in, use the Field function to strip out the correct portion and append it to the existing Stage Variable.
3) Put the two stage variables on separate output links, and put a constraint of LastRow() on the links so they only output one record at the very end. You can combine them back into one stream with a Funnel stage (set to "ordered" so they stay in order).
I'm still concerned that if you have a ton of records, you'll blow through the limits of a single variable. If that happens I'd probably look at using UNIX commands to reformat the data. You might also be able to "build" a sequential file with the correct Record options, but I don't have time to look into that.
Posted: Thu Aug 22, 2013 9:34 am
by skp
Thanks a lot Andy... I will try this and let you know any problems...
Posted: Fri Aug 23, 2013 12:44 am
by skp
Hi Andy,
how to stri out the correct portion using field function please help me on this.
Posted: Fri Aug 23, 2013 1:10 am
by ArndW
If your In.Record = "a b"
Then Field(In.Record,' ',1) will be "a"
and Field(In.Record,' ',2) will be "b".
The first parameter is the string,
the second parameter is the field delimiter,
and the third parameter is the field number to extract.
Posted: Fri Aug 23, 2013 1:38 am
by skp
Andy,
My input record like below:
Col1
a
b
c
d
each record come in next line not in the same line.
Posted: Fri Aug 23, 2013 3:55 am
by ray.wurlod
Build the output lines in VarChar stage variables.
Output them on separate output links keeping only the final version of each, using either LastRecordInGroup() function or downstream RemoveDuplicates stages. Base these on a "generated" constant "key".
Bring them together using a Funnel stage.
Posted: Fri Aug 23, 2013 6:15 pm
by rameshrr3
Try awk based unix script .
This awk script will work even if your source file has an arbitrary number of columns ( you currently have 2 delimited by what i think is a space character)
Code: Select all
awk -F" "
'{for (f = 1; f <= NF; f++) a[NR, f] = $f}
NF > nf { nf = NF }
END
{ for (f = 1; f <= nf; f++)
for (r = 1; r <= NR; r++)
printf a[r, f] (r==NR ? RS : FS)}' <YourSourceFileName>
You can call this script as a filter command in Sequential File stage or create a script and call it with your file name as argument in an External source stage.
Thanks
Ramesh