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

Subfield with apply map function

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 ValidValue 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

1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

 

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

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

2 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

 

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

Help users find answers! Don't forget to mark a solution that worked for you!
vikasshana
Creator II
Creator II
Author

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