Generic and cross table data load

[ericdelaqua]

Hello ,

I need some help with my scenario. I have the table below and not sure how to get the desired output.

Source Data

Items	fields	datedata	textdata	numdata
1	Date	08/12/2020
1	origin		japan
1	amount			900
2	Date	08/12/2020
2	origin		Korea
2	amount			1200

Expected output

Items	Date	origin	amount
1	08/12/2020	Japan	900
2	08/12/2020	Korea	1200

thank you

[Vegar]

If you are satisfied with a static set of columnsl in the table then you can do something like this.

Table:

Load * inline [Item] ;

For each _field in 'Date|datedata', 'origin|textdata', 'amount |numdata' 

Let vField = subfield('$(_field)','|', 1)

Let vColumn = subfield('$(_field)','|', -1)

LEFT JOIN (Table) LOAD 

Item, [$(vColumn)]  as [$(vField)] 

FROM Source

Next _field

[MayilVahanan · Accepted solution]

Hi @ericdelaqua 

One more method with generic load

InputTable:
LOAD Items, fields, RangeMaxString(datedata, textdata, numdata) as data INLINE [
Items, fields, datedata, textdata, numdata
1, Date, 08/12/2020, ,
1, origin, , japan,
1, amount, , , 900
2, Date, 08/12/2020, ,
2, origin, , Korea,
2, amount, , , 1200
];

GenTable:
Generic Load Items, fields, data Resident InputTable;

ResultTable:
LOAD Distinct Items Resident InputTable;

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'GenTable.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
NEXT i

DROP TABLES TableList, InputTable;

[ericdelaqua]

Hi Mayil, 

I will apply both methods to the actual dataset and revert. Thank you 

Eric