topic Re: Optimize the speed of load of a pivot table in QlikView

Optimize the speed of load of a pivot table

caccio88 — Fri, 25 Sep 2015 16:41:54 GMT

Hi guys,

who could help me please.

In my QV there a pivot table that's made by a ValueList as dimension.

So there are so many expressions based on that ValueList.

The problem is that cause a QV so slow and when u load the file and when u make a filter.

I attach here my QV, "Labour Cost_2", to take a look at the script of the expressions in the pivot table.

My own goal would to make quicker the app. Maybe changing the formula of the expressions without using the ValueList or with others solution.

Thank u so much.

Re: Optimize the speed of load of a pivot table

Gysbert_Wassenaar — Fri, 25 Sep 2015 17:14:16 GMT

Change your data model so that the values you now have in the valuelist are in a table and linked to the appropriate accounting items. Perhaps this blog post can help: How IntervalMatch Solved My Profit and Loss Dilemma

Re: Optimize the speed of load of a pivot table

caccio88 — Sat, 26 Sep 2015 16:43:09 GMT

Hi gwassenaar‌ and thaks for your help.

But I'm not so sure to understand what's your solution related to my QV.

Could u please explain me your solution in another way, please?

I just have watched the video of How IntervalMatch Solved My Profit and Loss Dilemma‌ , but I can't understand how IntervalMatch could solve my optimization.

Thank u

Re: Optimize the speed of load of a pivot table

Gysbert_Wassenaar — Sat, 26 Sep 2015 18:39:05 GMT

Start by creating a table in the script with the values in your valuelist and the accounting items that should be grouped with those items:

LOAD * INLINE [

Group, Accounting Item

399-TOT. RETRIBUZIONE LORDA. 399-TOT. RETRIBUZIONE LORDA

1999-TOT. RETRIBUZIONE IMPONIBILE,399-TOT. RETRIBUZIONE LORDA

1999-TOT. RETRIBUZIONE IMPONIBILE,Festività (lavorate)

1999-TOT. RETRIBUZIONE IMPONIBILE,Indennità varie

1999-TOT. RETRIBUZIONE IMPONIBILE,Straordinari

1999-TOT. RETRIBUZIONE IMPONIBILE,Premi/UT

1999-TOT. RETRIBUZIONE IMPONIBILE,Assenteismo

1999-TOT. RETRIBUZIONE IMPONIBILE,Altri Bdg Old

...etc

];

Then use Group as dimension in your pivot table. And you can use a simpler expression like

num(Sum({$<[HQ/BU]={'Unit'},DateYearMonthID="$(=Max(DateYearMonthID))"},DateFiscalYear=,DateFiscalQuarter=,DateMonth=>} Value),'#.##0')

You don't have to use an inline table. You could create it in an excel file too and load it from the excel file. Or perhaps you already have that information available in another source you can load from.

Re: Optimize the speed of load of a pivot table

caccio88 — Sat, 26 Sep 2015 21:01:55 GMT

Right than u so much gwassenaar . I think that is my solution but I've met a new problem...

That's my exact load inline:

LOAD * INLINE [

Group, Accounting Item

399-TOT. RETRIBUZIONE LORDA, 399-TOT. RETRIBUZIONE LORDA

1999-TOT. RETRIBUZIONE IMPONIBILE, 1999-TOT. RETRIBUZIONE IMPONIBILE

3299-TOT. CONTRIBUTI, 3299-TOT. CONTRIBUTI

4099-TOT. TFR, 4099-TOT. TFR

4599-TOT. ACCANTONAMENTI, 4599-TOT. ACCANTONAMENTI

4999-COSTO GENERALE, 4999-COSTO GENERALE

5101-COSTO GENERALE, 5101-COSTO GENERALE

TOTALE X CDG, TOTALE X CDG

COOPERATIVE, PULIZIE AFFIDATE A TERZI

COOPERATIVE, PERSONALE COOPERATIVE PALMAR

PRESTATORI DI OPERA, PRESTATORI DI OPERA

TOTALE MAP, TOTALE X CDG

ORE LAVORATE DIPENDENTI, TOTALE ORE EFF. LAVORATE (RIPARTITE)

COSTO MEDIO ORARIO DIPENDENTI, TOTALE X CDG / TOTALE ORE EFF. LAVORATE (RIPARTITE)

ORE LAVORATE PRESTATORI DI OPERA, Ore Lavorate Interinali

COSTO MEDIO ORARIO PRESTATORI DI OPERA, PRESTATORI DI OPERA / Ore Lavorate Interinali

];

As u can see "COSTO MEDIO ORARIO DIPENDENTI" and "COSTO MEDIO ORARIO PRESTATORI DI OPERA" are derived from a division. But with this script the formula doesn't work...

How can I solve it??

Re: Optimize the speed of load of a pivot table

caccio88 — Mon, 28 Sep 2015 09:29:40 GMT

Do u think that is possible to get around the obstacle gwassenaar‌?

Re: Optimize the speed of load of a pivot table

Gysbert_Wassenaar — Mon, 28 Sep 2015 13:00:08 GMT

Yes, I think it's possible. I did something like this not too long ago. I'll see if I can find it again.

Re: Optimize the speed of load of a pivot table

caccio88 — Mon, 28 Sep 2015 13:04:51 GMT

Wonderful. I hope so. thanks gwassenaar

Re: Optimize the speed of load of a pivot table

Gysbert_Wassenaar — Mon, 28 Sep 2015 13:15:31 GMT

See this discussion: Year over Year (YOY) comparison based on Dimensions

Re: Optimize the speed of load of a pivot table

caccio88 — Thu, 01 Oct 2015 10:01:33 GMT

Yep, gwassenaar‌! Fantastic! That's exactly what I was looking for! You've gave me the chance to optimize so many application with this new concept! You opened my QV's "script mind"! I've just advanced your suggestion with others fields and I've been able to do wonderful things!

Thank u legend! So grateful!