

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Generic and cross table data load
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Please close the thread by marking correct answer & give likes if you like the post.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Mayil,
I will apply both methods to the actual dataset and revert. Thank you
Eric
