topic If condition with multiple values in expression in QlikView

If condition with multiple values in expression

neena123 — Mon, 01 Oct 2018 20:39:40 GMT

Hello!!

I need some help on creating an expression that selects all orders with any one of these combinations:

Last Status:520 Next Status 540

Last Status:523 Next Status 540

Last Status:525 Next Status 540

Here is what I got

IF([Last Status]=523 AND [Next Status]=540,COUNT(DISTINCT([Unique Order Key])),IF([Last Status]=520 AND [Next Status]=540,COUNT(DISTINCT([Unique Order Key])),IF([Last Status]=525 AND [Next Status]=540,COUNT(DISTINCT([Unique Order Key])))))

I tried multiple ways and nothing seems to work. Any help is appreciated!!!

Re: If condition with multiple values in expression

vishsaggi — Mon, 01 Oct 2018 20:42:26 GMT

may be try this?

= Count(DISTINCT IF(Match(LastStatus, 520,523,525)>0 AND NextStatus = 540, UniqueOrderKey))

Re: If condition with multiple values in expression

neena123 — Mon, 01 Oct 2018 21:02:48 GMT

Hi Vishwarath,

Thanks for the reply! I actually tried that but sadly it doesn't work. I think it reads it and sees the 520 status and matches with that first so it only brings in data with a last status of 520 and Next status 540

Re: If condition with multiple values in expression

vishsaggi — Mon, 01 Oct 2018 21:22:25 GMT

can you share some sample data and expected output that would help?

Re: If condition with multiple values in expression

maxgro — Mon, 01 Oct 2018 21:34:02 GMT

try this

count({$ <[Next Status]={540}, [Last Status]={520,523,525}>} DISTINCT [Unique Order Key])

Re: If condition with multiple values in expression

neena123 — Mon, 01 Oct 2018 21:43:20 GMT

Sure! I just had to scramble some of the data.