replace sales value with manipulated one

[wlpike]

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

[JordyWegman]

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 

[wlpike]

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

[JordyWegman]

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

[wlpike]

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, 

[wlpike]

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?

[JordyWegman]

Do you have sample QVF also for this one? This makes a bit easier to test!

Jordy

Climber

[wlpike]

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.