topic Re: Match Function not getting work with Dates in QlikView

Match Function not getting work with Dates

chandu441 — Sat, 16 Nov 2024 18:18:52 GMT

Hi All,

Match was not working for the below scenario.

I am selecting some multiple date values in the list box it has to show value '1' else '0'.

But in my case when i am selecting multiple values it is showing '0' value.

my expression is

=if( Match( Date, '12/20/2019','12/21/2018' ), 1 ,0)

In another trail, what ever I am selecting values I am storing as a variable and passing the variable in the expression even though it is showing incorrect answer.

vDateMulti =concat( distinct chr(39) & Date(Date) ,chr(39) &',') &chr(39)

=if( Match( Date, $(vDateMulti) ), 1 ,0)

According to the selected dates i have to pass some set analysis measures in the place of '1' & '0'.

Appreciate for the help.

Thanks

Chandu

Re: Match Function not getting work with Dates

dplr-rn — Fri, 24 Jul 2020 19:55:38 GMT

you need to convert vDateMulti into a date format. right now its text.

try using Date# on vDateMulti before comparision

Re: Match Function not getting work with Dates

Kushal_Chawda — Fri, 24 Jul 2020 22:09:43 GMT

Match function evaluates the value for each rows when used in load or in chart's expression. So if you are using this formula in text object or variable it will not work as there is no dimensions for multiple selections . But if you select the single value it will work because it's just a single record.

So, if you want to use this condition apart from load statement or chart's expressions. You need to do something like below

if( concat(Date,',')= '12/20/2019,12/21/2018' , 1 ,0)

Re: Match Function not getting work with Dates

chandu441 — Sat, 25 Jul 2020 07:47:24 GMT

Hi Dilipranjith,

In Back end script I am defining the Date field as below:-

Date#( Date(Date) , 'MM/DD/YYYY') as Date,

In front end Text object I am checking the below conditions true or not:-

vDateValue=Chr(39)& Replace( GetFieldSelections(Date),',',Chr(39)&','&chr(39) ) & Chr(39)

=if( Match( Date, $(vDateValue) ), 1 ,0)

vDateMulti=concat( distinct chr(39) & Date ,chr(39) &',') &chr(39)
=if( Match( Date, $(vDateMulti) ), 1 ,0)

Here Date filters are not static, User may select any of the dates in front end according to the given date filters i need to apply some logic set analysis in true condition.

Attached is the fact table example in excel.

Thanks

Chandu441

Re: Match Function not getting work with Dates

chandu441 — Sat, 25 Jul 2020 07:45:59 GMT

Hi Kush/MVP,

I have checked your expression in text object

if( concat(Date,',')= '12/20/2019,12/21/2018' , 1 ,0) it is working and showing value '1'.

But my Date values are not static it is dynamic user selections.

So I have created the variable 'vDateValue' =Chr(39)& Replace( GetFieldSelections(Date),',',Chr(39)&','&chr(39) ) & Chr(39)

Used in your expression as =if( concat(Date,',')= $(vDateValue) , 1 ,0) I am getting error

Issue was attached to the reply..

Thanks

Chandu441

Re: Match Function not getting work with Dates

Kushal_Chawda — Sat, 25 Jul 2020 11:24:42 GMT

change the expression of variable as below

=Chr(39)& Concat(Date,',')& Chr(39)

then use below

if( concat(Date,',')= $(vDateValue) , 1 ,0)