Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

Multiply with Default value

Hi,

I've below two QVD's.

QVD1:

CODE Valid Value
AAA 2023 0.12
BBB 2023 0.23

 

QVD2:

ID SALES VALUE CODE COUNTRY
1 100 AAA INDIA
2 200 BBB INDIA
3 300 CCC AUS
4 500 DDD US

 

So in my script I'm multiplying SALES VALUE from QVD2 with Value in QVD1 like below.

[SALES VALUE] * Applymap('QVD1',CODE,0) as New_Sales_Value

and as a result below is the table which I receive

ID SALES VALUE CODE New_Sales_Value COUNTRY
1 100 AAA 12 (100*0.12) INDIA
2 200 BBB 46 (200*0.23) INDIA
3 300 CCC   AUS
4 500 DDD   US

 

For ID 3 & 4 the New_Sales_Value is 'blank' because CODE 'CCC' & 'DDD' is not there in QVD1. So my requirement is if the entry is not there in QVD1 would like to make default value as '1', below is the output which I'm expecting

ID SALES VALUE CODE New_Sales_Value COUNTRY
1 100 AAA 12 (100*0.12) INDIA
2 200 BBB 46 (200*0.23) INDIA
3 300 CCC 300 (300 * 1) AUS
4 500 CCC 400 (400 * 1) US

 

I'm looking for a QlikView script in order to achieve it.

Regards,

Vikas

 

 

 

 

 

 

 

 

 

 

 

1 Solution

Accepted Solutions
Digvijay_Singh

Shouldn't this below statement gives '0' in the row 3rd and 4th. it should be Sales value * 0 = 0

and if you want 1 then 0 could be replaced with 1.

[SALES VALUE] * Applymap('QVD1',CODE,0) as New_Sales_Value

 

Not sure if I am missing something, as  understand, 3rd parameter in applymap is used when value is not present. If you want 1 then it could have 1 instead of 0.

View solution in original post

2 Replies
Digvijay_Singh

Shouldn't this below statement gives '0' in the row 3rd and 4th. it should be Sales value * 0 = 0

and if you want 1 then 0 could be replaced with 1.

[SALES VALUE] * Applymap('QVD1',CODE,0) as New_Sales_Value

 

Not sure if I am missing something, as  understand, 3rd parameter in applymap is used when value is not present. If you want 1 then it could have 1 instead of 0.

vikasshana
Creator II
Creator II
Author

Thanks for that, after posting even I though that if we replace 0 with 1 it will be resolved. Now I've replaced 0 with 1 and the issue is fixed.