Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a problem that I am trying to answer and I am stuck.
I have two tables: HEADER header DETAIL:
HEADER:
claim_id net_payment
12345 24.59
DETAIL:
claim_id diagnosis
12345 A
12345 B
12345 C
12345 D
Is there a simple way to transform these two tables (HEADER and DETAIL), so that I get a table that looks like this?:
SAMPLE_TABLE:
claim_id net_payment Diagnosis_1 Diagnosis_2 Diagnosis_3 Diagnosis_4
12345 24.59 A B C D
Any help is highly appreciated.
Thank you in advance!!
Marcin
Hi Marcin,
Generic Load is an option. But you need to create a postion number first:
RawData:
LOAD * INLINE [
claim_id,diagnosis
12345,A
12345,B
12345,C
12345,D
67890,E
67890,F
67890,G
67890,H
67890,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;
..and then a consolidation of all created tables from Generic Load.
- Ralf
Marcin
Sounds like you are need of a Generic Load.
This post has an example.
Best Regards, Bill
Which post, Bill?
Thanks!
Hi Marcin,
Generic Load is an option. But you need to create a postion number first:
RawData:
LOAD * INLINE [
claim_id,diagnosis
12345,A
12345,B
12345,C
12345,D
67890,E
67890,F
67890,G
67890,H
67890,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;
..and then a consolidation of all created tables from Generic Load.
- Ralf
Works like a charm!!!
Thank you QlikView Masters!
🙂