topic Re: If and match question in QlikView

If and match question

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

I have the following load script...

Temp:

LOAD

if(Min(date([ClientGroupInterventionInterventionDate], 'dd/mm/yyyy')) and match(ClientGroupInterventionModeofServiceCode, '1', '2', '22', '23', '24', '25', 'PH_CLINICAL'), 'FDOOS', 'NFDOOS') as FirstDirectInterventionDate,

if(Max(date([ClientGroupInterventionInterventionDate], 'dd/mm/yyyy')) and match(ClientGroupInterventionModeofServiceCode, '1', '2', '22', '23', '24', '25', 'PH_CLINICAL'), 'LDOOS', 'NLDOOS') as LastDirectInterventionDate

Resident ClientGroupInterventions;

LET vFirstDirectInterventionDate = floor(peek('FirstDirectInterventionDate'));

LET vLastDirectInterventionDate = floor(peek('LastDirectInterventionDate'));

SET vDirectRangeStart = =vFirstDirectInterventionDate;

SET vDirectRangeEnd = =vLastDirectInterventionDate;

and am getting the following error

Invalid expression

Temp:

LOAD

Resident ClientGroupInterventions

I can't figure out what is going wrong! Any help would be appreciated!

Re: If and match question

PrashantSangle — Thu, 04 May 2017 08:48:53 GMT

Hi,

Remove your max() and min() from if condition and then try..

Value return by min() and max() should compare with some value then only if condition will execute.

Regards

Re: If and match question

Peter_Cammaert — Thu, 04 May 2017 08:55:00 GMT

Min() and Max() are aggregation functions that operate on one or more rows in a table, usually classified into sets by way of a GROUP BY.

However, when loading & processing single rows at a time, Min() and Max() are invalid.

You may want to first find Min and Max values for all categories in a separate step, and then either JOIN the min/max results to your individual rows, or use ApplyMap to pick them out when LOADing individual rows.

Also not that Min() or Max() on their own as a logical expression will only evaluate to fales if an exact 0 is returned. Don't you want to compare the result of the Min() and Max() Calls to something else?