Subfield with apply map function

vikasshana — Wed, 13 Sep 2023 07:33:05 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 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

Re: Subfield with apply map function

Aurelien_Martinez — Wed, 13 Sep 2023 08:27:41 GMT

Hi,

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

Re: Subfield with apply map function

vikasshana — Thu, 14 Sep 2023 14:06:55 GMT

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

topic Re: Subfield with apply map function in QlikView

Subfield with apply map function

Re: Subfield with apply map function

Re: Subfield with apply map function