topic Re: How to flatten multiple records into a single record? in QlikView

How to flatten multiple records into a single record?

mszuberl — Tue, 10 Dec 2013 18:37:53 GMT

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

Re: How to flatten multiple records into a single record?

Anonymous — Tue, 10 Dec 2013 18:44:04 GMT

Marcin

Sounds like you are need of a Generic Load.

This post has an example.

Best Regards, Bill

Re: How to flatten multiple records into a single record?

mszuberl — Tue, 10 Dec 2013 19:07:00 GMT

Which post, Bill?

Thanks!

Re: How to flatten multiple records into a single record?

rbecher — Tue, 10 Dec 2013 19:09:48 GMT

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

Re: Re: How to flatten multiple records into a single record?

Anonymous — Tue, 10 Dec 2013 19:22:08 GMT

Marcin

Ooops!!

Generic Load

Best Regards, Bill

Re: How to flatten multiple records into a single record?

mszuberl — Tue, 10 Dec 2013 19:31:39 GMT

Works like a charm!!!

Thank you QlikView Masters!

🙂