Hi
I have an interesting requirement and I would like to find a systematic solution for it. I would appreciate if you could give me some hints to implement the logic. ( I searched the forum but could not find a good answer)
input file is a sequential file with the format below
zzzzz a b c ...... n
date1 a1 b1 c1 ......n1
date2 a2 b2 c2 ......n2
date3 a3 b3 c3 ......n3
................................
................................
dateX ax bx cx ......nx
I am reading the file as one record as the number of columns is unknown
Number of rows is also unknown as you can guess. in fact the file is comma delimited.
My desired output format will be in a data set of 2 columns
The "zzzz" is garbage and will be thrown out. All the "a" values will be concatenated as well as the "b" and "c" values etc. the date column is same for every row but it is again concatenated with the all the dates in the order of incoming rows.
value ........................ date
--------------------- --------------------------------
a a1 a2 a3 .... an date1 date2 date3 .....datex
b b1 b2 b3 .... bn date1 date2 date3 .....datex
c c1 c2 c3 ...... cn date1 date2 date3 .....datex
......................... ........................................
......................... ........................................
n n1 n2 n3 .....nx date1 date2 date3 .....datex
I am able to handle the date part easily, the value part requires vertical pivot with unknown number of columns which I think is not possible.
vertical pivot for variable number of rows and columns
Moderators: chulett, rschirm, roy
vertical pivot for variable number of rows and columns
Last edited by mctny on Sun Jun 19, 2011 9:50 am, edited 1 time in total.
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Thanks Craig for the reply, That's the thing, as of now I don't have a safe number that I can assume as the maximum number of columns. ( I can go back to the business Analysts and ask if they can give me such a number)
is it possible to do in a transformer with a loop where the loop limit is the number of delimiter in the first row?
is it possible to do in a transformer with a loop where the loop limit is the number of delimiter in the first row?
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes. Stage variables are evaluated before loop variables, so you could count the delimiters into a stage variable then build your loop exit condition based on @ITERATION being less than that value.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks Ray, it still does not help me because in that case, I have to create dynamic number of stage variables (equal to the number of delimiters which will be different for each run). if possible then the logic will be simple, when rows comes, I will keep concatenating the incoming value of the stage variable.
is such a thing possible? can I create n number of stage variables? if not possible, is there any other approach?
is such a thing possible? can I create n number of stage variables? if not possible, is there any other approach?
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Not really. Within the loop, use the Field() function to extract the nth field where n = @ITERATION (the current value of the loop counter).
Code: Select all
svDelimitedFieldCount = DCount(InLink.DelimitedField, ",")
Loop
While @ITERATION <= svDelimitedFieldCount
lvNthField = Field(InLink.DelimitedField, ",", @ITERATION, 1)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks to you both, Yes I can do that and it will help me extract all the elements of the matrix with the order a,b, c, ..n, a1, b1, c1,... n1,a2,b2,c2,...cn... but I am not sure it will help me to achieve my desired output, it will only help me to concatenate ai, bi, ci ... ni ( where i is 1,2 .. n), however I want to concatenate a1, a2, a3...an. if I have to rephrase I need to skip n elements to append all a's, b's etc.
Yes stage variable is available in loop but note that loop variable is not available in a stage variable derivation. and I can not save the loop variable ( lvNthField) across the incoming rows in n different loop variables
Yes stage variable is available in loop but note that loop variable is not available in a stage variable derivation. and I can not save the loop variable ( lvNthField) across the incoming rows in n different loop variables
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"