Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Aryasmank
Contributor III
Contributor III

Conversion from if statement to pick and match function

Hi all, 

I have to change the if statement to the pick and match function, but there is "greater than" in if statement. I'm unable to find out how to do that. 

Below is my expression :

if([Date] = Date($(MaxDate)), $(tostate),
if([Date] > Date($(MaxDate)),
if(linest_m( total aggr(if(count(id_CR) ,count(id_CR)),[Date] ),[Date] ) >=0,

$(tostate)- RangeSum( Above(linest_m( total aggr(if(count(id_CR) ,count(id_CR)),[Date] ),[Date] )
*[Date.FiscYear.YearMonth]
+linest_b( total aggr(if(count(id_CR) ,count( id_CR)),[Date] ),[Date] )
,0,(((year([Date] )*12)+month([Date] ))-((year(Date($(MaxDate)))*12)+month(Date($(MaxDate)))))
))

,
$(tostate)- RangeSum(above($(laststate)
,0,(((year([Date] )*12)+month([Date] ))-((year(Date($(MaxDate)))*12)+month(Date($(MaxDate)))))
))

)))

Aryasmank_0-1667916402968.png

 

 

If anyone has any inputs please share.

Thanks 

M

#if-else  #pick-match  #optimization

 

Labels (3)
3 Solutions

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

To code your Match using varying expressions, use "-1" (true) as first argument and code it like this:

Match(-1, [Date] = Date($(MaxDate), [Date] > Date($(MaxDate), etc...

Why are you converting from If() to Pick(Match())? if it's for optimization my experience is that pick(match()) performs the same as if(). 

-Rob

View solution in original post

marcus_sommer

I do agree with Rob that there won't be a significantly performance difference between if() and pick(match()). But you may optimize your calculations itself. For example, the part of: if(count(id_CR) ,count(id_CR)) might be just replaced with count(id_CR).

Also, the continuous period-counter of (year([Date]*12+month([Date])) could be already generated within the script and the period-counter to the variable outsourced within another variable and maybe even also created within the script (if the max-date is always a quite fixed value like today() or similar).

Whereby I wouldn't expect a much better performance from these measures because the general approach is quite resource expensive - nested if-loops with aggr() within interrecord-functions won't be never really fast. Therefore you may need a re-design of your object/view and/or the underlying data-model.

- Marcus

View solution in original post

marcus_sommer

Something like: year([Date]*12+month([Date]) could be added within a master-calendar and would then be available for all calculations.

- Marcus

View solution in original post

6 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

To code your Match using varying expressions, use "-1" (true) as first argument and code it like this:

Match(-1, [Date] = Date($(MaxDate), [Date] > Date($(MaxDate), etc...

Why are you converting from If() to Pick(Match())? if it's for optimization my experience is that pick(match()) performs the same as if(). 

-Rob

Aryasmank
Contributor III
Contributor III
Author

Hi ,

Thanks for the response, 

Yes, I'm converting it for optimization , as you said , pick(match()) performs the same as if(), can you please suggest any other option for optimising the above expression . It will really helpful.

Thanks,

Mayank

marcus_sommer

I do agree with Rob that there won't be a significantly performance difference between if() and pick(match()). But you may optimize your calculations itself. For example, the part of: if(count(id_CR) ,count(id_CR)) might be just replaced with count(id_CR).

Also, the continuous period-counter of (year([Date]*12+month([Date])) could be already generated within the script and the period-counter to the variable outsourced within another variable and maybe even also created within the script (if the max-date is always a quite fixed value like today() or similar).

Whereby I wouldn't expect a much better performance from these measures because the general approach is quite resource expensive - nested if-loops with aggr() within interrecord-functions won't be never really fast. Therefore you may need a re-design of your object/view and/or the underlying data-model.

- Marcus

Aryasmank
Contributor III
Contributor III
Author

Hi, thanks for the input , 

if possible, can you please explain, little more , the second para about continuous period-counter . It will really helpful.

Note: the MaxDate is also contain the nested if expression 

 

thanks ,

Mayank

marcus_sommer

Something like: year([Date]*12+month([Date]) could be added within a master-calendar and would then be available for all calculations.

- Marcus

Aryasmank
Contributor III
Contributor III
Author

Thanks