Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ericdelaqua
Creator
Creator

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

Itemsfieldsdatedatatextdatanumdata
1Date08/12/2020  
1origin japan 
1amount  900
2Date08/12/2020  
2origin Korea 
2amount  1200

 

Expected output

ItemsDateoriginamount
108/12/2020Japan900
208/12/2020Korea1200

 

thank you

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

3 Replies
Vegar
MVP
MVP

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

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
ericdelaqua
Creator
Creator
Author

Hi Mayil, 

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

Eric