Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sakshikaul
Creator II
Creator II

Multiple filters used from different tables using apply map

Dear All,

I have an excel which is maintained by user manually. It is having  columns for eg dealer code, manufacturer_ name.

Now i want this manufacturer should map against the dealer code so this could be done with the help of using apply map function.

like:- table1:

mapping load Dealer code

manufacturer_name 

so i simply  will use apply map('table1',Dealer code,'NA') to get the above required condition 

but what if my mapping load will have more than 2 columns for eg :- I have dealer code, manufacturer_name,service_no etc 

How to use apply function in this case?

what is the alternative to implement the same ?

@sunny_talwar  and @Anil_Babu_Samineni   Please help its urgently required 

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

I'm sorry, but I don't see any blue.

Your applymaps looks OK. If you want to concat the output into one field you should not use "AND" you need to use "&"

ApplyMap('Central_Report_Dealer_Code',[Dealer Code],'') & ApplyMap('Central_Report_Claim_Type',[Claim type]','NA')

View solution in original post

Vegar
MVP
MVP

You need to remove the single quote (') after [Claim type] 

 

View solution in original post

10 Replies
Vegar
MVP
MVP

The map tables are limited to having only two columns. You can't use three columns in a map table.

To solve your need you can either create two different map tables. Or you can use a  join to combine the data of your tables. Just make sure that you three columns table does not have supplicates dealer codes, else you could will be duplicating the transactions in your original main table.

sakshikaul
Creator II
Creator II
Author

Hi,

I have following conditions to implement on the script level. They are as follows

Untitled.png

like for eg - I have condition as if part no=1 and Service No. is any value which is maintained in some other excel then I simply wrote a script as 

If(part_no= 1,ApplyMap('Functional_Service_ID',[Service No.],'')) As [Functional Test], so I was able to implement like this similarly, Now I have multiple conditions like 1. amount >2000 and claim type=9-10  2. service ID =4840 and Mileage <500 and so on then how to implement same in the script when we have multiple filters...

Please help....

 

Vegar
MVP
MVP

You can have multiple conditions in an if statement. Try something like this.

 

IF(((amount>2000) AND match(type,9,10)) OR ((ServiceID=4840) and (Mileage<500)),
     ApplyMap('Functional_Service_ID',[Service No.],''),
     null()
  ) as FTest

 

sakshikaul
Creator II
Creator II
Author

Thanks for your immediate reply. 

I have one more concern like the conditions provided in excel keeps on changing every week so I want when certain condition is not fulfilled it should work as a blank filter and rest of the conditions should be checked.

For Eg:-I have to  apply condition 

IF(((amount>2000) AND match(type,9,10)) OR ((ServiceID=4840) and (Mileage<500)),
     ApplyMap('Functional_Service_ID',[Service No.],''),
     null()
  ) as FTest

and next day again user changes condition as I have to apply condition claim type =6-10 instead of 9-10 and amount<1000 instead of > 2000 and rest other conditions like service_ no will be blank as it is not required to be checked under new condition then in that case how to handle?

Saravanan_Desingh

Mapper:
Mapping
LOAD DCode&'@'&Manufacture&'@'&Service# As Key, Rating
;
LOAD * INLINE [
    DCode, Manufacture, Service#, Rating
    D1, Toyota, S1, 2
    D2, Honda, S3, 1
    D3, GM, S4, 3
];

tab1:
LOAD *, ApplyMap('Mapper',DealerCode&'@'&ManufactureName&'@'&ServiceNo,'N/A') As DealerRating
;
LOAD * INLINE [
    DealerCode, ManufactureName, ServiceNo
    D1, Toyota, S1, 2
    D2, Honda, S3, 1
    D4, GM, S4, 3
];
sakshikaul
Creator II
Creator II
Author

In simple words I want to implement the below situation 

Ex : If Row 4 has Service ID column  as 2706 and Damage Type Column as 0010, then it will work like 2706+0010. So ideally each row will work like unique condition.

In simple terms, Rows are conditions, columns are combinations.

Now how to implement this using apply map function ?

sakshikaul
Creator II
Creator II
Author

I want to implement nested apply maps in a single line.
 
I tried below coding but its giving me a syntax error
mapping tables.........
Central_Report_Dealer_Code:
mapping LOAD
[Dealer Code],
'Dealer_Code'
 
FROM
[D:\Qlik \ Claim\WPRC_Raw_Data_AU\Central report conditions - revised.xlsx]
(ooxml, embedded labels, table is Sheet1);
Central_Report_Claim_Type:
mapping
LOAD
     [Claim type],
     'Claim_Type'
FROM
[D:\Qlik \ Claim\WPRC_Raw_Data_AU\Central report conditions - revised.xlsx]
(ooxml, embedded labels, table is Sheet1);
 ApplyMap('Central_Report_Dealer_Code',[Dealer Code],'') and  ApplyMap('Central_Report_Claim_Type',[Claim type]','NA')  
 
Following condition marked in bold is giving me syntax error...Please help.
 
I have to apply all the conditions using apply map given in excel sheet
 

Untitled.png

Vegar
MVP
MVP

I'm sorry, but I don't see any blue.

Your applymaps looks OK. If you want to concat the output into one field you should not use "AND" you need to use "&"

ApplyMap('Central_Report_Dealer_Code',[Dealer Code],'') & ApplyMap('Central_Report_Claim_Type',[Claim type]','NA')

sakshikaul
Creator II
Creator II
Author

sakshikaul_0-1587117443807.png

I am trying the code you mentioned above but I am getting some syntax error in this.