topic Re: Multiply with Default value in QlikView

Multiply with Default value

vikasshana — Fri, 08 Sep 2023 14:55:24 GMT

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

Re: Multiply with Default value

Digvijay_Singh — Fri, 08 Sep 2023 16:57:43 GMT

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.

Re: Multiply with Default value

vikasshana — Mon, 11 Sep 2023 07:32:21 GMT

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.