topic Re: SUM with filters in a LOAD script? in QlikView

SUM with filters in a LOAD script?

Diego_780 — Fri, 24 Jun 2022 09:31:58 GMT

Hello,

So I have a table in which I want to do a sum to a dimension but with one filter lets say, so for example, i want to sum quantity of products when this other dimension from the same load script is equal to "AS", I know how to sum that in expressions in a table but when I put this expression in the load script it doesnt eat it:

Here it is on a graph (pivot table):

And I want this calculated field to be in my load:

How can I achieve this? Also, I need to do another one with differente ID_TIPO and then take these two calculated dimensions and operate with them (one minus the other one), all in the load script, because I want to store into a qvd.

And yes, I know you can store the pivot table with its data if you click on the table and export it but I need to do it in a load script so it stores it automatically if I program a task, so I don´t have to export manually each time I want to refresh the data

Thanks.

Re: SUM with filters in a LOAD script?

sergio0592 — Fri, 24 Jun 2022 11:28:40 GMT

Hi,

Try with:

LOAD if(ID_TYPO='SAP',CANTIDAD_TOTAL,0) as CANTIDAD_SAP, ID_TYPO, ID_TYPO2, ...

Re: SUM with filters in a LOAD script?

rwunderlich — Fri, 24 Jun 2022 16:00:24 GMT

Use a Where clause in the Load.

LOAD
Sum(CANTIDAD_TOTAL) as CANTIDAD_SAP,
ID_TYPO2,
From ...
Where ID_TIPO = 'SAP'
Group By ID_TYP02,...

Re: SUM with filters in a LOAD script?

Diego_780 — Mon, 11 Jul 2022 10:58:59 GMT

I'm trying it with your code but I can´t achieve what I really want, so I have this on the pivot table with this expressions for each type: SUM({<ID_TIPO = {"SAP"}>} CANTIDAD_TOTAL) or SUM({<ID_TIPO = {"VISUAL"}>} CANTIDAD_TOTAL) and in the pivto table it shows like this, in ONE line:

I´ve tried this code for a normal table:

But the result is this:

How can I get both values in the same line?? They have everything in common unless the ID_TYPE, that one is 'SAP' and the other 'VISUAL' but I thought that by using JOIN it would merge into one.

Thank you.

Re: SUM with filters in a LOAD script?

rwunderlich — Mon, 11 Jul 2022 14:08:43 GMT

Your rows will not merge in a Join because ID_TIPO values are different. You should be able to create both fields in a single load like:

Sum(if(ID_TIPO = 'SAP', CANTIDAD_TOTAL, 0) as CANTIDAD_SAP,
Sum(if(ID_TIPO = 'VISUAL', CANTIDAD_VISUAL, 0) as CANTIDAD_VISUAL,

And remove ID_TIPO from the LOAD and the GROUP BY.

-Rob

Re: SUM with filters in a LOAD script?

Diego_780 — Tue, 12 Jul 2022 08:17:18 GMT

Thanks for the answer! It finally worked, but I had to remove another value, in this case CANTIDAD_TOTAL (Total quantity) because it was different so it was making me the split, I had to remove if from the LOAD and the Group By aswell. Now it works fine thanks, also had to do a minus between both values and no problem, I just used the minus symbol in between and name it a new value:

Sum(If(ID_TIPO='SAP', CANTIDAD_TOTAL,0)) - Sum(If(ID_TIPO='VISUAL', CANTIDAD_TOTAL,0)) as DESCUADRE2

Thanks for the help folks!