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!
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
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.
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.
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.
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?
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.