if condition requirement

[vikasshana]

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

[rubenmarin]

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];