2 Replies Latest reply: Oct 13, 2016 9:56 AM by Maxime Dumas RSS

    Join + Group By in Qlik Sense

    Maxime Dumas



      I am trying to translate this sql query into a load script using a qvd file. The idea is to precalculate fees for the trailing 12 months at load to speed up things. This result is used in a complex score calculation that must be precalculated, so I cannot use a calendar to do it on the fly. PositionMonthlySnapshot is a 235 millions rows table.


      Here is the SQL that would do the expected behavior:

      SELECT client_sk, d.snapshot_date, sum(firmcur_management_fees) FROM bi_poc_demo.position_monthly_fact p

      join bi_poc_demo.t12_dates d on p.snapshot_date = d.t12_date

      group by client_sk, d.snapshot_date


      Here is the load script:

      [T12 Months]:


        Floor([Snapshot Date]) as [Snapshot Date Master],

        Floor(MonthEnd(AddMonths([Snapshot Date], -IterNo() + 1))) as [Snapshot Date]

      Resident [Snapshot Dates]

      While IterNo() <= 12;


      [Position T12 Tmp]:

      LOAD [Client ID], [IA Code ID], [Branch ID], [Security ID], Floor([Snapshot Date]), [Position Management Fees],

      FROM 'lib://QVDLib/PositionMonthlySnapshot.qvd' (qvd);


      INNER JOIN ([Position T12 Tmp])

      LOAD [Snapshot Date], [Snapshot Date Master]

      RESIDENT [T12 Months];


      [Position T12]:

      LOAD [Client ID], [Snapshot Date Master], SUM([Position Management Fees])

      RESIDENT [Position T12 Tmp]

      GROUP BY [Client ID], [Snapshot Date Master];


      Although this script would work, the join between [Position T12 Tmp] and [T12 Months] causes a 235M x 12 table, which makes me run out of RAM. Is there a better way to do this using Qlik, or will I have to do it in the warehouse?



        • Re: Join + Group By in Qlik Sense
          Robert Mika

          Is this full script?

          I can not see any drop table statement

          I believe the T12Months is redundant?


          What is the join between Position T12 tmp and the next Table?

          Is ClientID the key?

            • Re: Join + Group By in Qlik Sense
              Maxime Dumas

              No it's not the full script, just the part equivalent to the SQL. The T12 Table is deleted after the join. T12Months contains the 12 months for each [Snapshot Date]. The idea is to sum the [Position Management Fees] for each T12 for each month. The expected result is:


              Client IDSnapshot DateT12 Fees
              1Jan 31 2015456.42
              1Dec 31 2014235.24



              So basically, I need a T12 calculation for each entry in position_monthly_fact.