Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
i am new to Qlik Sense and i need to get a script running, where i want to do following:
i have lots of sales data and corresponding products.
i need to get the gap in product sales between two products and add it as additional product.
i hope it is clear what i need.
i thought of something like following code in LOAD script:
if(wildmatch(PRDCTH0CS, 'BJK'), (SALES_1R1), 0) AS SumBJK,
if(wildmatch(PRDCTH0CS, 'AJK'), (SALES_1R1), 0) AS SumAJK
if (wildmatch(PRDCTH0CS, 'BJK'),SumBJK-SumAJK,SALES_1R1) as sales
unfortunately the manipulation in line 3 is not working.
can anybody help me with this ?
thanks a lot!
br
chris
Hi Chris,
Create a mapping table for this and execute this in the script.
MapSalesValue2Manipulated:
Mapping Load
Key,
Value
From [YourSource] (qvd)
;
Then execute this in your script:
ApplyMap('MapSaleValue2Manipulated',Key,[OrginalSalesField]) as NewCreatedField
Jordy
Climber
Hi Jordy,
Thanks but am i right to use mapping table for new calculation.
I need to get new calculation to original sales field.
So Key would be my product code and value the new calculation using my sum attibutes in load area.
Correct?
Many thanks
Br
Chris
Yes that is correct. You can rename your original field to [SalesField2] and create your adjusted sales field with the original name. This way you have adjusted values with the original name.
Jordy
Climber
Hi Jordy,
i am i little bit confused 🙂
i need to maniulate the sales value of one product and to do that i need to calculate the gap in sales between two products.
i used following code but i get product "BJK" only. i dont know what went wrong.
MapSalesValue2Manipulated:
Mapping Load
PRDCTH0CS,
if(wildmatch(PRDCTH0CS, 'BJK'), (SALES_1R1), 0) - if(wildmatch(PRDCTH0CS, 'AJK'), (SALES_1R1), 0) as sales1
From [lib://Workshop (dch2)/BW_Export_20190625_2018only.qvd] (qvd)
;
---------------
in main load script i used following, that should check
ApplyMap('MapSalesValue2Manipulated',PRDCTH0CS,SALES_1R1) as SalesNew
when i get appymap correctly, sales values that are not in original list are added,
when i use
if(wildmatch(PRDCTH0CS, 'BJK'), (SALES_1R1), 0)
for getting the sum of sales for product BJK, all i get is the count value, so it seems i just get how many fields there are, but not the actual value of sales...
could this be a bug in Qliksense?
Do you have sample QVF also for this one? This makes a bit easier to test!
Jordy
Climber
Hi Jordy,
actually, you have it already.
the manipulation in load script and using variable in chart-section are two types of reaching the same goal that i tried.