Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I've below data.
ID | start date | end date | type | sales amount | ID1 | running ID | previous ID |
1 | 16/03/2021 | 15/03/2023 | test | 100 | 100 | 2 | |
2 | 16/03/2020 | 15/03/2021 | test2 | 0 | 100 | 1 | |
3 | 16/03/2020 | 15/03/2021 | test3 | 0 | 100 | 1 |
Now I've the requirement from the business is previous ID has to choose the highest sales amount, if both previous ID's shares the same information (like in this case it has same start date, end date and sales amount) it has to pick test3.
ID | start date | end date | type | sales amount | ID1 | running ID | previous ID |
1 | 16/03/2021 | 15/03/2023 | test | 100 | 100 | 3 | |
2 | 16/03/2020 | 15/03/2021 | test2 | 0 | 100 | ||
3 | 16/03/2020 | 15/03/2021 | test3 | 0 | 100 | 1 |
Regards,
V
Hi, this could be done in script, in a sorted load and using peek() to propagate the value, like:
LOAD
ID,
[start date],
...
If(Peek([start date])=[start date] and Peek([end date])=[end date] and [other conditions to check]...
,Peek([previous ID])
,ID) as [previous ID]
residnet table
order by [start date], [end date], [other neede fields to sort];