Hi,
Here's Henric's application of loops to combine this set of data into one table. I've used this quite a few times in my reports and is excellent piece of code to keep on hand when you need to unpivot your data.
The great thing about it is that it doesn't matter how many tables you have in your data model, it will pick up only those tables in your generic load to join back to your distinct list of keys.
This example only has 3 columns (first being the key, second becomes the column headers, third becomes the values).
Only if there are more than 3 columns will you need to create an autonumber field to join back to.
Link to the post:
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470
If you copy the below code into a new Qlik session you'll be able to see how it works. I've added commentary to each part of the code to explain what it does.
OriginalData:
load * inline [
ID NAME VALUE
1 name1 val1
1 name2 val2
2 name1 val3
2 name2 val4
3 name1 val5
4 name2 val6
3 name1 val7
4 name2 val8
5 name3 val9
](delimiter is '\t');
// Using Generic Load will prefix each of the output tables with "GenericLabel" (Eg. GenericLabel.name1)
GenericLabel:
generic
Load
ID,
NAME,
VALUE
Resident OriginalData;
Set vListOfTables = ; // Create an empty variable
For vTableNo = 0 to NoOfTables() // Set the counter from 0
Let vTableName = TableName($(vTableNo)) ; // Loop through all tables in your data model
If Subfield(vTableName,'.',1)='GenericLabel' Then //If the first part of the table name before the '.' is "GenericLabel" then do the below step
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; // This saves a comma separated list of the GenericLabl tables
//eg 'GenericLabel.name1','GenericLabel.name2'
//chr(39) is ascii for an apostrophe
End If
Next vTableNo
//Create a distinct list of your key to join to below
CombinedGenericTable:
Load distinct
ID
Resident OriginalData;
//Finally loop through each table from the vListOfTables created above to join to your CombinedGenericTable
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)]; //Drops each of the GenericLable tables as they are read in cleaning up your data model
Next vTableName
