Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try using a Generic load to pivot the data
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
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
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