Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Try with:
LOAD
if(ID_TYPO='SAP',CANTIDAD_TOTAL,0) as CANTIDAD_SAP,
ID_TYPO,
ID_TYPO2,
...
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,...
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.
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
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!