Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Ohh heyy!!!
Thankyou soo muchh !!!