topic Re: calculation of Minimum dates using group by clauses. in QlikView

calculation of Minimum dates using group by clauses.

deepaktibhe — Fri, 15 Nov 2024 23:15:37 GMT

Hi All,

Below is the condition that is being used in our application.

[Factory KPI]:
LOAD *
WHERE Not IsNull ([Factory KPI Status]);
LOAD [Sales Document Item Key],
'Production' as [Factory KPI Group],
'Late to Production SO Stock' as [Factory KPI],
If ([Production Order DLV Date] > [Basic Finish Date], 'Action', 'OK') as [Factory KPI Status];
LOAD [Sales Document Item Key],
Min ([Production Order DLV Date]) as [Production Order DLV Date],
Min ([Basic Finish Date]) as [Basic Finish Date]
RESIDENT Temp
GROUP BY [Sales Document Item Key];

It seems that the highlighted part consumes a long reload time.

Can you please suggest some alternative ways to get rid of this?

Thanks in advance.

Thanks

Re: calculation of Minimum dates using group by clauses.

edwin — Mon, 25 Jan 2021 15:59:13 GMT

if you have a huge data set, you may want to move this logic (the entire code above) to your data layer (possibly an ETL) this may be easy for a data base server.

Re: calculation of Minimum dates using group by clauses.

deepaktibhe — Mon, 25 Jan 2021 17:04:18 GMT

Hi Edwin,

Thanks for your valuable suggestion. I am looking forward to reducing the overall reload time.

But, moving the logic to the ETL side would be a time-consuming task.

If we can use variables for min and max dates and use the same variable wherever possible.

Will that help. Please advise.

Thanks

Re: calculation of Minimum dates using group by clauses.

edwin — Mon, 25 Jan 2021 19:26:11 GMT

at the end of the day, if you need to get the min of those dates per sales doc item key, then you will have to do an aggregation. variables will not work here as a variable can only contain a single value and cramming multiple values in a single variable would be complex and time consuming to parse.

you are aware that preloads are executed last to first. meaning the aggregation will execute before the one with the where clause WHERE Not IsNull ([Factory KPI Status]);

perhaps (and you need to benchmark this), you can move the criteria to the first load statement. and i think the only case when Factory KPI Status is null is when a sales item id has no [Production Order DLV Date] or no [Basic Finish Date]
-> which means min of either will be null
-> which means [Factory KPI Status] will be null
and since you apparently dont need it, remove it at the start. it reduces the volume and reduces the loads

so to reduce the aggregation add a where clause

WHERE not isnull([Production Order DLV Date]) and not isnull([Basic Finish Date])

and you dont need the first load statement:
LOAD *
WHERE Not IsNull ([Factory KPI Status]);

of course this will have to be tested and your data profiled: are there a lot of sales item id with null dates? if the answer is no there will always be a date then the first load statement is not needed as the field [Factory KPI Status] will never be null. thats what i mean by profiling

hope that helps