topic Re: Multiple filters used from different tables using apply map in QlikView

Multiple filters used from different tables using apply map

sakshikaul — Thu, 16 Apr 2020 03:37:31 GMT

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

Re: Multiple filters used from different tables using apply map

Vegar — Thu, 16 Apr 2020 04:44:30 GMT

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.

Re: Multiple filters used from different tables using apply map

sakshikaul — Thu, 16 Apr 2020 07:57:42 GMT

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

Re: Multiple filters used from different tables using apply map

Vegar — Thu, 16 Apr 2020 09:48:21 GMT

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

Re: Multiple filters used from different tables using apply map

sakshikaul — Thu, 16 Apr 2020 11:30:29 GMT

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?

Re: Multiple filters used from different tables using apply map

Saravanan_Desingh — Fri, 17 Apr 2020 03:18:34 GMT

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 ];

Re: Multiple filters used from different tables using apply map

sakshikaul — Fri, 17 Apr 2020 05:23:17 GMT

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 ?

Re: Multiple filters used from different tables using apply map

sakshikaul — Fri, 17 Apr 2020 09:48:19 GMT

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

Re: Multiple filters used from different tables using apply map

Vegar — Fri, 17 Apr 2020 09:49:02 GMT

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')

Re: Multiple filters used from different tables using apply map

sakshikaul — Fri, 17 Apr 2020 09:58:21 GMT

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

Re: Multiple filters used from different tables using apply map

Vegar — Sun, 19 Apr 2020 07:09:14 GMT

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