Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
DiegoFraj
Contributor III
Contributor III

SUM with filters in a LOAD script?

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):

DiegoFraj_0-1656062785152.png

 

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

DiegoFraj_1-1656062886008.png

 

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.

Labels (1)
1 Solution

Accepted Solutions
rwunderlich

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

 

View solution in original post

5 Replies
sergio0592
Specialist III
Specialist III

Hi,

Try with:

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

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,...

DiegoFraj
Contributor III
Contributor III
Author

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:

DiegoFraj_0-1657536887436.png

I´ve tried this code for a normal table:

DiegoFraj_1-1657537034615.png

But the result is this:

DiegoFraj_2-1657537057346.png

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.

rwunderlich

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

 

DiegoFraj
Contributor III
Contributor III
Author

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

DiegoFraj_0-1657613807993.png

Thanks for the help folks!