Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mszuberl
Contributor III
Contributor III

How to flatten multiple records into a single record?

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

1 Solution

Accepted Solutions
rbecher
MVP
MVP

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

Astrato.io Head of R&D

View solution in original post

5 Replies
Anonymous
Not applicable

Marcin

Sounds like you are need of a Generic Load.

This post has an example.

Best Regards,     Bill

mszuberl
Contributor III
Contributor III
Author

Which post, Bill?

Thanks!

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Anonymous
Not applicable

Marcin

Ooops!!

Generic Load



Best Regards,     Bill

mszuberl
Contributor III
Contributor III
Author

Works like a charm!!!

Thank you QlikView Masters!

🙂