If and match question

[ckb12345]

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

     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

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

[PrashantSangle]

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

[Peter_Cammaert]

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?