topic Optimization Technique in QlikView

Optimization Technique

noman212 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

Please find below the scenario i am currently facing in Qlikview. We have 2 Billion rows showing sales data.

I have to uniquely identify the shopcode irrespective of dimensions selected. i am using below expression to evaluate the result but dashboard is taking too much time. KIndly suggest the optimiation technique to imporove performance.

ZONE	REGION	SHOPCODE	EXPRESSION	peek()
Zone1	Region1	1001	count(distinct SHOPCODE)	1
Zone1	Region1	1002	count(distinct SHOPCODE)	1
Zone1	Region2	1001	count(distinct SHOPCODE)	0
Zone1	Region2	1002	count(distinct SHOPCODE)	0
Zone1	Region2	1002	count(distinct SHOPCODE)	0
Zone1	Region1	1001	count(distinct SHOPCODE)	0

I used the peek function but issue is that if I filter data on region level, the record is missed.

Re: Optimization Technique

phaneendra_kunc — Mon, 08 Aug 2016 06:58:29 GMT

How long it is taking to render? 15-20 mins for each selection?

Is there a reason you have to keep all 2 billion rows in one QVW?

Not sure i understood our problem on getting unique code and using a peek. Can you elaborate what you are trying to achieve.

Re: Optimization Technique

noman212 — Mon, 08 Aug 2016 07:07:20 GMT

Dear Phaneendra,

Thanks for the reply.

1. Yup. it takes 5-10 minutes to evaluate expression and throws connection lost error.

2. It is sales data and since the data is to be analyzed for past 2-3 years, therefore a single dashboard file is used.

3. Let me again define the issue. I want to uniqely identify the shop based on dimension selected. To overcome the performance issue, i used the peek function to tag 1 or 0 against each shop and then finally use sum() to get unique shops. The issue is that 0 exist against shops for second region.

Re: Optimization Technique

phaneendra_kunc — Mon, 08 Aug 2016 07:17:36 GMT

Gotcha.

Seems like your PEEK statement needs adjustment. If you notice peek marked first two shop codes 1001,1002 for region 1 and for rest it marked as 0 since i believe you are checking if shop code is not same then 1 else 0.

So as a solution you need to have 1's in all those rows correct?

Can you share your peek function?

With out using this peek may be you can try this appraoc as well.

Try creating a table with distinct Shop Codes and a counter flag (1 as flag).

so basically table will have below and that will connect to your fact shopcode as key. in UI all you have to do SUM(Flag)

Shopcode, flag

1001,1

1002,1

1003,1

1004,1

Re: Optimization Technique

noman212 — Mon, 08 Aug 2016 07:34:48 GMT

Seems logical.

Let me try this i will get back to you if there is any discrepancy.