How to flatten multiple records into a single record?

[mszuberl]

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

[Anonymous]

Marcin

Sounds like you are need of a Generic Load.

This post has an example.

Best Regards,     Bill

[mszuberl]

Which post, Bill?

Thanks!

[rbecher · Accepted solution]

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

[Anonymous]

Marcin

Ooops!!

Generic Load

Best Regards,     Bill

[mszuberl]

Works like a charm!!!

Thank you QlikView Masters!

🙂