Subfield with apply map function

[vikasshana]

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 Valid & Value in QVD1 like below.

Main1:
Mapping Load CODE, Valid&';'&Value Resident QVD1;

NoConcatenate
Main2:
Load *,[SALES VALUE] * SubField(Applymap('Main1',CODE,0),';',2) as New_Sales_Value Resident QVD2;

Drop Table QVD1,QVD2;

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 tried modifying the apply map function from 0 to 1 like below but same issue.

[SALES VALUE] * SubField(Applymap('Main1',CODE,1),';',2) as New_Sales_Value

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

Regards,

Vikas

[Aurelien_Martinez · Accepted solution]

Hi,

Have you try : [SALES VALUE] * SubField(Applymap('Main1',CODE, '1;1'),';',2) as New_Sales_Value

[vikasshana]

Thanks for that, implemented your solution and is working now.