topic Excluding ID's with IF and ApplyMap in QlikView

Excluding ID's with IF and ApplyMap

MisterK — Tue, 29 Oct 2019 15:05:31 GMT

Hi,

I would like some help with the following:

I'm working with a project that needs to separate certain ID's from a field. One ID (12345) was already separated before (by someone else), and now they want to exclude one more ID (34512). The code that was used to exclude the first ID is:

mapUniqueID:
MAPPING LOAD autonumber(%Key_FactoryOrder), [Unique ID]
FROM *****.qvd (QVD)
;

if(num(%Key_Dealer)=1 AND applymap('mapUniqueID',autonumber(%Key_FactoryOrder))='12345','12345',%Key_Dealer) AS [Retailer],

I've tried so many different options but with no luck. Is there a simple way to fix it? E.g.

if(num(%Key_Dealer)=1 AND applymap('mapUniqueID',autonumber(%Key_FactoryOrder))='12345','12345',%Key_Dealer,
if(num(%Key_Dealer)=1 AND applymap('mapUniqueID',autonumber(%Key_FactoryOrder))='34512','34515',%Key_Dealer)) AS [Retailer],

It should be quite easy but I've been sitting with it for a while now and I can't get it to work.

Sincerely
K

Re: Excluding ID's with IF and ApplyMap

rubenmarin — Tue, 29 Oct 2019 16:14:31 GMT

The syntax if is: If(condition, TrueActions, FalseActions).

Nested different If's (the FalseActions is another If): If(condition, TrueActions, If(condition, TrueActions, FalseActions)).

So tou can try this:

if(num(%Key_Dealer)=1 AND applymap('mapUniqueID',autonumber(%Key_FactoryOrder))='12345' ,'12345' , if(num(%Key_Dealer)=1 AND applymap('mapUniqueID',autonumber(%Key_FactoryOrder))='34512' ,'34515' ,%Key_Dealer ) ) AS [Retailer],

Re: Excluding ID's with IF and ApplyMap

sunny_talwar — Tue, 29 Oct 2019 16:53:09 GMT

Might be easier to just do this

If(Num(%Key_Dealer) = 1 and Match(ApplyMap('mapUniqueID', AutoNumber(%Key_FactoryOrder)), '12345', '34515'), ApplyMap('mapUniqueID', AutoNumber(%Key_FactoryOrder)), %Key_Dealer ) AS [Retailer],

Re: Excluding ID's with IF and ApplyMap

rubenmarin — Wed, 30 Oct 2019 08:18:09 GMT

I don't know why, but when I click the 'Like' button it seems that doesn't works.

Re: Excluding ID's with IF and ApplyMap

sunny_talwar — Wed, 30 Oct 2019 11:13:01 GMT

Not sure why would that be... seems to be working okay for me. Does this happen only for this post or any post?

Re: Excluding ID's with IF and ApplyMap

rubenmarin — Wed, 30 Oct 2019 11:21:56 GMT

Any post, I wasn't aware until now. I wonder how many likes has been lost

Re: Excluding ID's with IF and ApplyMap

sunny_talwar — Wed, 30 Oct 2019 11:24:47 GMT

That is strange, may be @Brett_Bleess can help us figure this out.