Previous Date in a new column

[alec1982]

hi,

I have a table as follow

Date		Store   Product	Value
12/25/2015	ABC	P1	400
12/25/2015	ABC	P2	200
12/25/2015	XYZ	P1	300
12/25/2015	XYZ	P2	200
12/27/2015	ABC	P1	400
12/27/2015	ABC	P2	200
12/27/2015	XYZ	P1	300
12/27/2015	XYZ	P2	200
12/28/2015	ABC	P1	400
12/28/2015	ABC	P2	200
12/28/2015	XYZ	P1	300
12/28/2015	XYZ	P2	200

Need add a column to show previous date. meaning the new column will have 12/27/2015 when the date=12/28/2015, 12/25/2015 when the date=12/27/2015 and null when date equal 12/25/2015.

Also, I have on the same logic for increment load so i need to apply the same logic later on for only new data..

I have tried Previous function but run into issues..

any idea?

[sunny_talwar]

Looking to do this in the script or front end?

[alec1982]

hi Sunny,

on the script. I tried to put distinct date into separate table and used previous function then joined to the original table but run into issues..

[sunny_talwar]

Something like this?

Script:

Table:

LOAD *,

  AutoNumber(Store & '|' & Product) as Key;

LOAD * Inline [

Date,   Store,   Product,   Value

12/25/2015, ABC, P1,     400

12/25/2015, ABC, P2,     200

12/25/2015, XYZ, P1,     300

12/25/2015, XYZ, P2,     200

12/27/2015, ABC, P1,     400

12/27/2015, ABC, P2,     200

12/27/2015, XYZ, P1,     300

12/27/2015, XYZ, P2,     200

12/28/2015, ABC, P1,     400

12/28/2015, ABC, P2,     200

12/28/2015, XYZ, P1,     300

12/28/2015, XYZ, P2,     200

];

FinalTable:

LOAD *,

  If(Key = Previous(Key), Previous(Date)) as Previous_Date,

  If(Key = Previous(Key), Previous(Value)) as Previous_Value

Resident Table

Order By Key, Date;

DROP Table Table;

[alec1982]

very close.. thank you.

the only one issue is that my sample has a kind of distinct values while the table I have has duplicated rows. I am fine to remove duplicates, any idea?

[sunny_talwar]

See this:

Table:

LOAD Distinct *,

  AutoNumber(Store & '|' & Product) as Key;

LOAD * Inline [

Date,  Store,  Product,  Value

12/25/2015, ABC, P1,    400

12/25/2015, ABC, P2,    200

12/25/2015, XYZ, P1,    300

12/25/2015, XYZ, P2,    200

12/27/2015, ABC, P1,    400

12/27/2015, ABC, P2,    200

12/27/2015, XYZ, P1,    300

12/27/2015, XYZ, P2,    200

12/28/2015, ABC, P1,    400

12/28/2015, ABC, P2,    200

12/28/2015, XYZ, P1,    300

12/28/2015, XYZ, P2,    200

12/25/2015, ABC, P1,    400

12/25/2015, ABC, P2,    200

12/25/2015, XYZ, P1,    300

12/25/2015, XYZ, P2,    200

12/27/2015, ABC, P1,    400

12/27/2015, ABC, P2,    200

12/27/2015, XYZ, P1,    300

12/27/2015, XYZ, P2,    200

12/28/2015, ABC, P1,    400

12/28/2015, ABC, P2,    200

12/28/2015, XYZ, P1,    300

12/28/2015, XYZ, P2,    200

];

TempTable:

NoConcatenate

LOAD DISTINCT *

Resident Table;

FinalTable:

LOAD *,

  If(Key = Previous(Key), Previous(Date)) as Previous_Date,

  If(Key = Previous(Key), Previous(Value)) as Previous_Value

Resident TempTable

Order By Key, Date;

DROP Tables Table, TempTable;

[Jacek]

Another example with previous date independent of the other columns:

Load script:

Table1:

Load * Inline [

Date, Store, Product, Value

12/25/2015, ABC, P1, 400

12/25/2015, ABC, P2, 200

12/25/2015, XYZ, P1, 300

12/25/2015, XYZ, P2, 200

12/27/2015, ABC, P1,    400

12/27/2015, ABC, P2, 200

12/27/2015, XYZ, P1, 300

12/27/2015, XYZ, P2, 200

12/28/2015, ABC, P1, 400

12/28/2015, ABC, P2, 200

12/28/2015, XYZ, P1, 300

12/28/2015, XYZ, P2, 200

];

Table2:

Load distinct

Date,

Date(Date#(Date,'MM/DD/YYYY')) as Date2

Resident Table1

Order by 2;

Left Join (Table1)

Table3:

Load

Date,

Date(Previous(Date2),'MM/DD/YYYY') as PrevDate

Resident Table2;

Drop Table Table2;

----------

Result:

[alec1982]

thank you

[sunny_talwar · Accepted solution]

No problem

[alec1982]

hi Sunny,

while the answer you gave me works well when i do full load, I am not sure how to move forward when i have incremental load.

for example we have done the full load based on your answer above. now I have if statement with incremental load to add only new data.. we need to apply similar logic after we do the incremental load without effecting the dates we already switched earlier on the full load....

any suggestions?