Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generic load example from QlikView Reference Manual with table consolidation.
// 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;
@ralfbecher

This comment has been minimized.

Copy link
Owner Author

@ralfbecher ralfbecher commented Dec 10, 2013

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;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.