LOAD new field using set analysis

[Anonymous]

Hi Experts,

I'm trying to insert a new field via the LOAD script called "End Date". This is the initial table:

Prim_key	last_date	Start Date
10001	3/31/2019	4/1/2016
10001	3/31/2019	4/1/2017
10001	3/31/2019	4/1/2018
10002	12/31/2020	1/1/2016
10002	12/31/2020	1/1/2018

End Date would be defined as follows:

• For each row, get the MIN Start Date (for particular Prim_key) that is greater than the Start Date of that active row. For example for the first row, Start Date is 4/1/2016. The values for all Start Date for Prim_key=10001 is 4/1/2016, 4/1/2017, 4/1/2018. The MIN out of all those values greater than the Start Date of the active row (4/1/2016) is 4/1/2017.
• Then subtract this value by 1. Using same example, the value for End Date for the first row would be 4/1/2017 minus 1 = 3/31/2017.
• IF there is no MIN Start (for particular Prim_key) that is greater than the Start Date of that row (another way of saying this is IF Start Date of current row = MAX Start Date for that Prim_key) then the value for End Date would be equal to last_date. So for example in the 3rd row, since there is no MIN Start Date for Prim_key=10001 greater than 4/1/2018 then End Date is equal to last_date, which is 3/3/1/2019.

I am having a hard time using set analysis to properly implement this in load script so that it is restricting the calculation to only within the set of values for a particular Prim_key as opposed to getting the min values in the entire table.

Final loaded table should look like this:

Prim_key	last_date	Start Date	End Date
10001	3/31/2019	4/1/2016	3/31/2017
10001	3/31/2019	4/1/2017	3/31/2018
10001	3/31/2019	4/1/2018	3/31/2019
10002	12/31/2020	1/1/2016	12/31/2017
10002	12/31/2020	1/1/2018	12/31/2020

[Anil_Babu_Samineni]

May be like attached

Expression used

If([Start Date]=Date(Max(TOTAL <Prim_key> [Start Date])),Date([last date]),Date(Below(TOTAL [Start Date])-1))

[maxgro · Accepted solution]

I'm trying to insert a new field via the LOAD script called "End Date".

if you want in load script

Table:

LOAD * INLINE [

    Prim_key, Start Date, last date

    100, 4/1/2016, 3/31/2019

    100, 4/1/2017, 3/31/2019

    100, 4/1/2018, 3/31/2019

    200, 1/1/2016, 12/31/2020

    200, 1/1/2018, 12/31/2020

];

Table2:

LOAD

*,

IF(Prim_key = Peek('Prim_key'), Date(Peek('Start Date')-1), [last date]) as [End Date]

Resident Table

Order By Prim_key, [Start Date] desc;

DROP Table Table;