Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
As the attached image, the date should show 2/5/2019 on the second column.
How can I apply the logic to change the data position?
Regards,
Leo
DATE(MAX(AGGR(DATE(MAX(FakeDate),'D/M/YYYY'), UserID)),'D/M/YYYY')
load * inline [
UserID,FakeDate, CostCenter
1, 2/5/2019, B
1, 1/4/2019, A
2, 1/4/2019, C
];
Try this:
AGGR(MAX(IF(FakeDate<=Date,FakeDate)),UserID,CostCenter).
I have tried the formula. However, it is not correct.
What I want is the second date should be null, only showing the first record and the third record.
Do you mean you want to sort the null value as the last record?
UserID | Formula for Date | CostCenter |
1 | 2/5/2019 | B |
1 | - | A |
2 | 1/4/2019 | C |
Just like this way to show the content.
Add the word NODISTINCT into the Aggr function: Aggr(NODISTINCT Max(...etc
Try this one as well: MAX(TOTAL <UserID> IF(FakeDate<=Date,FakeDate))
Can i get your raw data?
DATE(MAX(AGGR(DATE(MAX(FakeDate),'D/M/YYYY'), UserID)),'D/M/YYYY')
load * inline [
UserID,FakeDate, CostCenter
1, 2/5/2019, B
1, 1/4/2019, A
2, 1/4/2019, C
];