Is there any logic to the way the data is received?
From you example it looks like you receive 3 columns at time? And the rows arrive as header row, data row, header row, data row?
If that is the case, I would try to the the data into 2 columns, one with the header row value, and one with the data value.eg
Code: Select all
Headers Data
ABC 123
DEF 234
XYZ 345
GHI 333
JKL 222
MNO 666
ABC 789
MNO 555
From here get a distinct list of header values and form a single column of delimited header values for all the values that are being input eg ABC|DEF|GHI|JKL|MNO|XYZ. Join this value to the existing input.
For the distinct list of header values give each one a position number.
1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 XYZ
At the same time apply a seuqence number to get the number of times header arrives (sort the data by headers and implement a counter for the header, increment by 1 and reset when the header name changes).
So by this point you can have your data looking like this
Code: Select all
Headers Data AllHeaders Count Position
ABC 123 ABC|DEF|GHI|JKL|MNO|XYZ 1 1
DEF 234 ABC|DEF|GHI|JKL|MNO|XYZ 1 2
XYZ 345 ABC|DEF|GHI|JKL|MNO|XYZ 1 6
GHI 333 ABC|DEF|GHI|JKL|MNO|XYZ 1 3
JKL 222 ABC|DEF|GHI|JKL|MNO|XYZ 1 4
MNO 666 ABC|DEF|GHI|JKL|MNO|XYZ 1 5
ABC 789 ABC|DEF|GHI|JKL|MNO|XYZ 2 1
MNO 555 ABC|DEF|GHI|JKL|MNO|XYZ 2 5
Resort your data by the count and the position
In a transformer create variables for
* Current Row (svCurrentRow)
* Change in Row (svRowChange)
* AllHeaders value (svHeaders)
* Count of Headers (ie DCount on All Headers) (svHeaderCount)
* Current Position (svCurrentPosId)
* Setting empty columns (svRepeatDelim)
* Current column value (svDataValue)
* Buliding the output (svBuildData)
* Previous Position (svPreviousPosId)
* Previous row (svPreviousRow)
Logic
svCurrentRow = lnk_data_in.Count
svRowChange = svCurrentRow<>svPreviousRow
svHeaderCount = DCount(lnk_data_in.AllHeaders,'|')
svCurrentPosId = lnk_data_in.Postion
svRepeatDelim = If svRowChange then svCurrentPosId-1 else svCurrentPosId-svPreviousPosId
svDataValue = lnk_data_in.Data
svBuildData = If svRowChange then Str('|',svRepeatDelim) : svDataValue else svBuildData:Str('|',svRepeatDelim) : svDataValue
svPreviousRow = lnk_data_in.Count
Set a constaint on the output as LastRowinGroup(lnk_data_in.Count)
Output the data to a sequential file with one column. Set the output column to be
If @OUTROWNUM = 1 then svHeaders : Char(10) : svBuildData else svBuildData
This should give you an output of
Code: Select all
Values
ABC|DEF|GHI|JKL|MNO|XYZ
123|234|333|222|666|345
789||||555|
I might have glossed over a few things, but this should get you pretty close. Assuming of course you can get your data into a format of column name and column value at the start.