You should be able to get what you are trying to achieve by doing a horizontal pivot followed by a vertical pivot.
Once your xml is in the format you describe you would do the following:
1. Insert a transformer where you pass all the original columns, but you would change the derivation for the Stat1...Stat13 values to:
Code: Select all
DSLink.FieldName:',Stat1,':DSLink.Stat1
2. Put in a pivot stage. You would have only 3 output columns:
Code: Select all
Columns Name Derivation
Group_Type
Group_Num
Field Stat1, Stat2, etc ... Stat13
3. Put in a column import stage
Output columns:
Group Type
Group_Num
FieldName
Stat_type
FieldNum
Format: Field Defaults:
Delimiter = comma
quote = none
Stage Properties:
Import Input Column = Field
Column Method Explict
Column to Import = FieldName
Column to Import = Stat_type
Column to Import = FieldNum
Input Tab:
Code: Select all
Partition Type = Hash; Perform Sort = Yes
Keys Usage
Group_Type Sorting, Partitioning
Group_Num Sorting, Partitioning
Field Sorting
4. Put in a Sort stage
Add output column keyChange
Code: Select all
Sorting Keys Mode
Group_Type Don't Sort
Group_Num Don't Sort
Stat_Type Sort
Create Key Change Column = True
5. Add a transform with the following stage variables
svMaxColumns = The Maximum number of columns
svCount = If lnkSort.keyChange then 1 else svCount+1
svBuildColumns = If lnkSort.keyChange then lnkSort.FieldNum else svBuildColumns :',': lnkSort.FieldNum
svOutput = svBuildColumns : str(',', svMaxColumns - svCount)
Output columns:
Code: Select all
Column Name Derivation
Group Type lnkSort.GroupType
Group Num lnkSort.Group_Num
Stat_type lnkSort.Stat_Type
Value svOutput
This will give a comma delimited output of all the columns up svMaxColumns
6. Add Remove Duplicates
Key by Group_Type, Group_Num, Stat_Type
Duplicate to Retain = Last
7. You can then output to a sequential file which will be comma delimited, no quotes. This should give you a file in the format you want. You can then create another job to read that file with the correct metadata for load into your database.
It might be possible to use a transformer here and replace the comma delimiter with @FM (Char(254)?) to keep it within the same job but I am not sure what would be required.
There is probably an easier way to implement this - Maybe using arrays, but that would take somebody smarter than me.