topic Re: Add condition to if match in QlikView

Add condition to if match

bellesol — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following calculate dimension:

=if(match(TYPE,'2041','2500','4000','2044','2040','3000','4990','3070','3090','2598') ,'2,000',if(match(TYPE,'1010') ,'0','1,000'))

this dimension display all the types from all the years, how can I added two condiotions to the calculation::

Year=year(Today())

month=Month(Today())

so the types that will be displayed are the types for current year and month

Thanks!

Kushal_Chawda — Thu, 09 Jan 2020 13:50:28 GMT

I would suggest you to move this if calculation to script as cal calculated dimension can cause performance issue.

LOAD*,

if(match(TYPE,'2041','2500','4000','2044','2040','3000','4990','3070','3090','2598') ,'2,000',if(match(TYPE,'1010') ,'0','1,000')) as TypeNew

Now your can use the simplify version of calculated dimension.

=aggr(only({<Year={"$(=year(today()))"},Month={"$(=month(today()))"}>}TypeNew) ,TypeNew)

If you further want to improve the expression, you can create a Flag for current year and current month condition is script

=if(year=year(today()) and month =month(today()),1,0) as Flag

Then instead of calculated dimension you can use the Flag in Expression set analysis with dimension TypeNew

bellesol — Thu, 09 Jan 2020 19:26:31 GMT

Thanks, it worked!

I'm sorry for the basic questions,

why did you use aggr? and why did you use the sign "$" in the set analysis?

Kushal_Chawda — Thu, 09 Jan 2020 20:00:24 GMT

I would recommend you to read this blogs

Kushal_Chawda — Thu, 09 Jan 2020 20:32:30 GMT

Also please don't forget to mark the reply as correct