Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Just need some help with set analysis. This is my data set.
Account Num | CreatedDateTime | Date | Month | Status |
C00001 | 3/31/2010 2:39:52 PM | 03/21/2010 | Mar | 0 |
C00001 | - | 03/31/2010 | Mar | 0 |
C00001 | - | 04/23/2010 | Mar | 2 |
C00001 | - | 04/30/2010 | Mar | 0 |
C00001 | 4/20/2015 8:57:35 AM | 04/20/2015 | Apr | 2 |
C00001 | 4/20/2015 11:50:15 AM | 04/20/2015 | Apr | 0 |
C00001 | - | 04/30/2015 | Apr | 0 |
I want to count only max(Status) of an particular [Account Num] for each Month. where status should be '0'. and there are many Other account numbers as above .
Ex. i only need to count those record which are having status = 0 for the max date of that month . like two of the record which are Bold ,i only need to count those. how can i do it in set analysis.
May be like this:
Sum(Aggr(If(FirstSortedValue(Status, -Date) = 0, 1, 0), AccountNum, Month))
May be like this:
Sum(Aggr(If(FirstSortedValue(Status, -Date) = 0, 1, 0), AccountNum, Month))
Thank you so much sunny it worked like a charm .
One more help if possible . what script i should write if i want to remove extra rows at back end and only want to keep the max(status) values for each Account number, month and year wise.
I don't see Year in your data, but assuming it is there, may be this:
Table:
LOAD [Account Num],
CreatedDateTime,
Date,
Month,
Year,
Status
FROM ....
Right Join (Table)
LOAD [Account Num],
Month,
Year,
Max(Date) as Date
Resident Table
Group By [Account Num], Month, Year;
Thank You for help, i will try it out