Hi all.
I have a source table and in this there are multiple rows per id according to different statuses of another field and also a date field, so data looks like this
ClientID Date Result Status OrderID
1 1/1/2020 0 A 1
1 1/1/2020 1 A 2
1 1/1/2020 0 B 3
1 2/1/2020 1 A 4
2 1/1/2020 0 C 5
2 1/1/2020 0 A 6
etc
What I was asked to is to make temporary tables of the above data for each Status like this:
Temp_Status_A:
LOAD ClientID,
max(OrderID) ,
max(Date)
resident Source_Table
where Status='A'
group by ClientID;
and so on for each status.
Now I am asked to count how many clients had at least a result=0 examining each status, but only taking into account the last Date for each one.
I tried
count(aggr(count({<[Date]={"=max("Date")"},[Result]={'0'}, [Status]={'A'}>+[Date]={"=max("Date")"},[Result]={'0'}, [Status]={'B'}>*[Date]={"=max("Date")"},[Result]={'0'}, [Status]={'C'}>} [ClientID]), [ClientID]))
but doesn't seem right.
I'd rather do it in script but i have no clue how to with all these new temp tables that don't include result at all (because i need the max date, and grouping by result also may give me two dates with 0 and 1 as results while i just want the max date).
Any help is appreciated.
Ioanna
