End Date from stat date of the next row.

[Brownie]

 Top

Can anyone help me out solve this problem. I need the last marketing's value to be todays Date.  also, the order must be same as this nothing should be different.

[RsQK · Accepted solution]

Hi, this should do it:

temp:
LOAD
ROWNO() AS original_sort,
EID,
DID,
DN,
DATE(DATE#(SD,'MM/DD/YYYY')) AS SD
INLINE [
EID|DID|DN|SD
1|1|IT|1/1/1995
1|2|Sales|12/1/2005
1|5|Finance|1/1/2015
2|1|IT|1/1/1995
2|3|Marketing|12/1/2008
3|2|Sales|1/1/1995
4|3|Marketing|1/1/2010
] (DELIMITER IS '|');

temp2:
NOCONCATENATE
LOAD * RESIDENT temp
ORDER BY EID,SD DESC;

DROP TABLE temp;

temp3:
LOAD *,
DATE(IF(PREVIOUS(EID)=EID,PEEK(SD)-1,TODAY())) AS EndDate
RESIDENT temp2;

DROP TABLE temp2;

Final:
NOCONCATENATE
LOAD * RESIDENT temp3
ORDER BY original_sort;

DROP TABLE temp3;
DROP FIELD original_sort;

[Brownie]

Ohh heyy!!! 

Thankyou soo muchh !!!