topic Merge data into a single row. in QlikView

Merge data into a single row.

Fri, 29 Aug 2014 10:48:23 GMT

Hello,

If anyone can help with the below problem I would be very grateful, thanks for your help in advance!

I have 2 tables the 1st a list of all policy facts and the 2nd table has all claims information.

The claims table is set out as follows:

LOAD

Policynumber,

ClaimNumber,

ClaimCost,

Claimdate

FROM

I.E:

PolicyNumber	ClaimNumber	ClaimCost	ClaimDate
Pol001	001	50	01/01/2014
Pol001	002	60	01/02/2014
Pol002	005	50	15/06/2014
Pol005	004	45	10/06/2014
Pol005	006	80	16/06/2014
Pol005	007	10	18/06/2014
Pol006	009	80	20/06/2014
Pol007	003	40	02/01/2014
Pol007	008	80	19/06/2014
Pol007	010	60	11/08/2014

I need to manipulate this table into 1 line of data per policy number as below.

Required new Table

PolicyNumber	Claim1number	Claim2number	Claim3number	Claim1cost	Claim2cost	Claim3cost	Claim1Date	Claim2Date	Claim3Date
Pol001	001	002	-	50	60	-	01/01/2014	01/02/2014	-
Pol002	005	-	-	50	-	-	15/06/2014	-	-
Pol005	004	006	007	45	80	10	10/06/2014	16/06/2014	18/06/2014
Pol006	009	-	-	80	-	-	20/06/2014	-	-
Pol007	003	008	010	40	80	60	02/01/2014	19/06/2014	11/08/2014

I assume I would then be able to left join this table to the Policy facts table.

Thanks

Gary

Re: Merge data into a single row.

Fri, 29 Aug 2014 10:56:48 GMT

Try using a Generic load to pivot the data

What is Generic load and when we used Generic load.

Re: Merge data into a single row.

marcus_sommer — Fri, 29 Aug 2014 11:33:52 GMT

Maybe you could calculates your needs already in script, like this:

t1:

Load

PolicyNumber,

concat(ClaimNumber, '|') as ClaimNumber,

count(ClaimNumber) as ClaimCount,

concat(ClaimCost, '|') as ClaimCostDetail,

sum(ClaimCost) as ClaimCost,

min(ClaimDate) as ClaimMinDate,

max(ClaimDate) as ClaimMaxDate

From xyz Groupb By PolicyNumber;

- Marcus

Re: Merge data into a single row.

MarcoWedel — Fri, 29 Aug 2014 12:59:04 GMT

Hi,

one solution:

tabClaimReport:
LOAD *,
    AutoNumber(RecNo(), PolicyNumber) as PolicyRow
FROM [http://community.qlik.com/thread/131252] (html, codepage is 1252, embedded labels, table is @1);

tabClaimDetailTemp:
Generic LOAD
  PolicyNumber,
  'Claim'&PolicyRow&'Number',
  ClaimNumber
Resident tabClaimReport;

tabClaimDetailTemp:
Generic LOAD
  PolicyNumber,
  'Claim'&PolicyRow&'Cost',
  ClaimCost
Resident tabClaimReport;

tabClaimDetailTemp:
Generic LOAD
  PolicyNumber,
  'Claim'&PolicyRow&'Date',
  ClaimDate
Resident tabClaimReport;

tabClaimDetail: 
LOAD Distinct PolicyNumber Resident tabClaimReport; 

DROP Table tabClaimReport; 

FOR i = NoOfTables()-1 to 0 STEP -1 
  LET vTab=TableName($(i)); 
  IF WildMatch('$(vTab)', 'tabClaimDetailTemp.*') THEN 
  LEFT JOIN (tabClaimDetail) LOAD * RESIDENT [$(vTab)]; 
  DROP TABLE [$(vTab)]; 
  ENDIF 
NEXT i

hope this helps

regards

Marco

Re: Re: Merge data into a single row.

MarcoWedel — Sat, 30 Aug 2014 18:36:43 GMT

or like this:

tabClaimReport:
LOAD *,
    AutoNumber(RecNo(), PolicyNumber) as PolicyRow
FROM [http://community.qlik.com/thread/131252] (html, codepage is 1252, embedded labels, table is @1);

FOR Each vClaimType in 'Number', 'Cost', 'Date'
  tabClaimDetailTemp:
  Generic LOAD
  PolicyNumber,
  'Claim'&PolicyRow&'$(vClaimType)',
  Claim$(vClaimType)
  Resident tabClaimReport;
NEXT vClaimType

tabClaimDetail: 
LOAD Distinct PolicyNumber Resident tabClaimReport; 

DROP Table tabClaimReport; 

FOR i = NoOfTables()-1 to 0 STEP -1 
  LET vTab=TableName($(i)); 
  IF WildMatch('$(vTab)', 'tabClaimDetailTemp.*') THEN 
  LEFT JOIN (tabClaimDetail) LOAD * RESIDENT [$(vTab)]; 
  DROP TABLE [$(vTab)]; 
  ENDIF 
NEXT i

hope this helps

regards

Marco