Join + Group By in Qlik Sense

maxim1500 — Thu, 13 Oct 2016 13:26:11 GMT

Hi,

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]:

Load

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?

Thanks!

Re: Join + Group By in Qlik Sense

robert_mika — Thu, 13 Oct 2016 13:36:33 GMT

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

maxim1500 — Thu, 13 Oct 2016 13:56:57 GMT

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 ID	Snapshot Date	T12 Fees
1	Jan 31 2015	456.42
1	Dec 31 2014	235.24

etc.

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

topic Join + Group By in Qlik Sense in App Development

Join + Group By in Qlik Sense

Re: Join + Group By in Qlik Sense

Re: Join + Group By in Qlik Sense