4 Replies Latest reply: Aug 30, 2014 2:36 PM by Marco Wedel RSS

    Merge data into a single row.

    Gary McDonald

      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 [F:\ClaimReport.xlsx]

      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

        • Re: Merge data into a single row.
          Dick Zeeman

          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

            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.
              Marco Wedel

              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.
                  Marco Wedel

                  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