Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Join + Group By in Qlik Sense

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!

2 Replies
Highlighted

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?

Highlighted
Partner
Partner

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

etc.

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