Last active
March 6, 2021 13:33
-
-
Save ralfbecher/6507943 to your computer and use it in GitHub Desktop.
Generic load example from QlikView Reference Manual with table consolidation.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// generic load with one consolidated resulting table: | |
InputTable: | |
LOAD * INLINE [ | |
object,attribute,value | |
ball,color,red | |
ball,diameter,10 cm | |
ball,weight,100 g | |
box,color,black | |
box,height,16 cm | |
box,length,20 cm | |
box,weight,500 g | |
box,width,10 cm | |
]; | |
/* resulting table (delimiter=tab): | |
object color diameter weight height length width | |
ball red 10 cm 100 g | |
box black 500 g 16 cm 20 cm 10 cm | |
*/ | |
GenTable: | |
Generic Load object, attribute, value Resident InputTable; | |
ResultTable: | |
LOAD Distinct object 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Another example where the field names are dependent from the position:
RawData:
LOAD * INLINE [
claim_id,diagnosis
12345,A
12345,B
12345,C
12345,D
67890,E
67890,F
67890,G
67890,H
67890,I
];
/* resulting table (delimiter=tab):
claim_id Diagnosis_1 Diagnosis_2 Diagnosis_3 Diagnosis_4 Diagnosis_5
12345 A B C D
67890 E F G H I
*/
InterimData:
LOAD claim_id, if(isnull(peek('claim_id')) or peek('claim_id')<>claim_id, 1, peek('position')+1) as position, diagnosis
Resident RawData;
GenTable:
Generic LOAD claim_id, 'Diagnosis_' & position as diagnosis, diagnosis as value
Resident InterimData;
ResultTable:
LOAD Distinct claim_id Resident InterimData;
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 RawData, InterimData, TableListe;