Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
wlpike
New Contributor III

replace sales value with manipulated one

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

 

7 Replies
Partner
Partner

Re: replace sales value with manipulated one

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 

Work smarter, not harder
wlpike
New Contributor III

Re: replace sales value with manipulated one

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

Partner
Partner

Re: replace sales value with manipulated one

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

Work smarter, not harder
wlpike
New Contributor III

Re: replace sales value with manipulated one

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
New Contributor III

Re: replace sales value with manipulated one

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?

Partner
Partner

Re: replace sales value with manipulated one

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

Jordy

Climber

Work smarter, not harder
wlpike
New Contributor III

Re: replace sales value with manipulated one

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.