if(match) question

[davyqliks]

Hi Again Experts,

I am trying to define in the script, if Status match 2 values, return 1 value. 

my issue is when Status match (only) expired, i am getting 'Valid' due the first line.

I need to define if match (Valid and Expired), but i cannot get this to work.

My match needs to be exact, so only if the status matches (Valid and Expired) will i get the return Valid.

I have the following: 

finalStatus:
Load
"Ex Date",
If(match(Status,'Valid','Expired'), 'Valid',
If(match(Status,'Valid','Due'), 'Valid',
If(match(Status,'Due','Expired'), 'Due',
If(match(Status,'Expired'), 'Expired'
)))) as FinalStatus
Resident Status

How can i make this exact search please, so to get Valid the status must equal both Valid and Expired.

thank you

Daniel

[rubenmarin · Accepted solution]

Hi, there are some things... each Match() evaluates a row, and in a row Status will only have one value, so none of the rows will have 'Valid' and 'Expired' at the same time. You'll ned to group them using some field so using a '|'& Concat(Distinct Status, '|') &'|' function or similar you can have all the different status of that grouping field in the same row

Then you can use Index() over that field to check the different status:

If(Index(Status,'|Valid|') and (Index(Status,'|Expired|') or Index(Status,'|Due|')),'Valid'...