Multiple filters used from different tables using apply map

[sakshikaul]

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 

[Vegar]

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]

Hi,

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

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]

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]

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]

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]

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

[Vegar · Accepted solution]

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]

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