Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Merge data into a single row.

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:

PolicyNumberClaimNumberClaimCostClaimDate

Pol001

001

50

01/01/2014

Pol001

0026001/02/2014
Pol0020055015/06/2014
Pol0050044510/06/2014
Pol0050068016/06/2014
Pol0050071018/06/2014
Pol0060098020/06/2014
Pol0070034002/01/2014
Pol0070088019/06/2014
Pol0070106011/08/2014

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

Required  new Table

PolicyNumberClaim1numberClaim2numberClaim3numberClaim1costClaim2costClaim3costClaim1DateClaim2DateClaim3Date
Pol001001002-5060-01/01/201401/02/2014-
Pol002005--50--15/06/2014--
Pol00500400600745801010/06/201416/06/201418/06/2014
Pol006009--80--20/06/2014--
Pol00700300801040806002/01/201419/06/201411/08/2014

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

Thanks

Gary

Tags (2)
4 Replies
Not applicable

Re: Merge data into a single row.

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.

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.

Hi,

one solution:

QlikCommunity_Thread_131252_Pic1.JPG.jpg

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.

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

Community Browser