Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mszuberl
New 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
MVP & Luminary
MVP & Luminary

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

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

5 Replies

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

Marcin

Sounds like you are need of a Generic Load.

This post has an example.

Best Regards,     Bill

mszuberl
New Contributor III

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

Which post, Bill?

Thanks!

MVP & Luminary
MVP & Luminary

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

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?

Marcin

Ooops!!

Generic Load



Best Regards,     Bill

mszuberl
New Contributor III

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

Works like a charm!!!

Thank you QlikView Masters!

:-)

Community Browser