Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table with fields:
ID | ID_POS | START_DATE | END_DATE | DAYS |
---|---|---|---|---|
COD00990 | 0 | 24/2/2012 | 23/2/2015 | 18 |
COD00990 | 1 | 24/2/2012 | 23/2/2015 | 18 |
COD00990 | 2 | 24/2/2012 | 23/2/2015 | 12 |
COD00991 | 0 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 1 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 2 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 3 | 23/5/2011 | 22/5/2015 | 12 |
COD00992 | 0 | 17/3/2015 | 15/5/2015 | 18 |
COD00992 | 1 | 17/3/2015 | 15/5/2015 | 18 |
COD00993 | 0 | 23/3/2017 | 22/6/2018 | 12 |
What I need to get is something like this by script:
ID | ID_POS | START_DATE | END_DATE | DAYS | EXTENSION_START_DATE | EXTENSION_END_DATE |
---|---|---|---|---|---|---|
COD00990 | 0 | 24/2/2012 | 23/2/2015 | 18 | 24/2/2015 | 14/3/2015 |
COD00990 | 1 | 24/2/2012 | 23/2/2015 | 18 | 15/3/2015 | 2/4/2015 |
COD00990 | 2 | 24/2/2012 | 23/2/2015 | 12 | 3/4/2015 | 15/4/2015 |
COD00991 | 0 | 23/5/2011 | 22/5/2015 | 12 | 23/5/2015 | 4/6/2015 |
COD00991 | 1 | 23/5/2011 | 22/5/2015 | 12 | 5/6/2015 | 17/6/2015 |
COD00991 | 2 | 23/5/2011 | 22/5/2015 | 12 | 18/6/2015 | 30/6/2015 |
COD00991 | 3 | 23/5/2011 | 22/5/2015 | 12 | 1/7/2015 | 13/7/2015 |
COD00992 | 0 | 17/3/2015 | 15/5/2015 | 18 | 16/5/2015 | 3/6/2015 |
COD00992 | 1 | 17/3/2015 | 15/5/2015 | 18 | 4/6/2015 | 22/6/2015 |
COD00993 | 0 | 23/3/2017 | 22/6/2018 | 12 | 23/6/2018 | 5/7/2018 |
To calculate the EXTENSION_START_DATE:
If ID_POS = 0, then EXTENSION_START_DATE = END_DATE + 1
If ID_POS > 0, the EXTENSION_START_DATE = PREVIOUS (EXTENSION_END_DATE) + 1
To calculate the EXTENSION_END_DATE:
EXTENSION_END_DATE = EXTENSION_START_DATE + DAYS
Is there any way to do this? I attach a sample QVW file and it's Excel with the data.
Thank you!!!
My Bad. I didn't realize that. Try
DATA:
Load
*,
Date(EXTENSION_START_DATE + DAYS) as EXTENSION_END_DATE
;
Load
*,
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0, Peek(EXTENSION_START_DATE) + DAYS + 1))) as EXTENSION_START_DATE
;
LOAD
ID,
ID_POS,
START_DATE,
END_DATE,
DAYS
FROM BD_Example.xlsx (ooxml, embedded labels, table is Example);
I don't know if I understood your question right, but you have posted the answer urself.
Anyway,
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0,PREVIOUS (EXTENSION_END_DATE) + 1))) as EXTENSION_START_DATE,
Date(EXTENSION_START_DATE + DAYS) as EXTENSION_END_DATE
Hello Vamsee,
The problem is that I don't have the fields EXTENSION_START_DATE and EXTENSION_END_DATE on the first place, I just have the following:
ID | ID_POS | START_DATE | END_DATE | DAYS |
---|---|---|---|---|
COD00990 | 0 | 24/2/2012 | 23/2/2015 | 18 |
COD00990 | 1 | 24/2/2012 | 23/2/2015 | 18 |
COD00990 | 2 | 24/2/2012 | 23/2/2015 | 12 |
COD00991 | 0 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 1 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 2 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 3 | 23/5/2011 | 22/5/2015 | 12 |
COD00992 | 0 | 17/3/2015 | 15/5/2015 | 18 |
COD00992 | 1 | 17/3/2015 | 15/5/2015 | 18 |
COD00993 | 0 | 23/3/2017 | 22/6/2018 | 12 |
So when I use this it doesn't find those fields:
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0,PREVIOUS (EXTENSION_END_DATE) + 1))) asEXTENSION_START_DATE,
Date(EXTENSION_START_DATE + DAYS) as EXTENSION_END_DATE
Yeah the above code still works.
It works because you're using the Extension_Start_Date and Extension_End_Date from the Excel file, but you shouldn't because we need to generate those fields using the original data we have, which is just this:
ID | ID_POS | START_DATE | END_DATE | DAYS |
---|---|---|---|---|
COD00990 | 0 | 24/2/2012 | 23/2/2015 | 18 |
COD00990 | 1 | 24/2/2012 | 23/2/2015 | 18 |
COD00990 | 2 | 24/2/2012 | 23/2/2015 | 12 |
COD00991 | 0 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 1 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 2 | 23/5/2011 | 22/5/2015 | 12 |
COD00991 | 3 | 23/5/2011 | 22/5/2015 | 12 |
COD00992 | 0 | 17/3/2015 | 15/5/2015 | 18 |
COD00992 | 1 | 17/3/2015 | 15/5/2015 | 18 |
COD00993 | 0 | 23/3/2017 | 22/6/2018 | 12 |
My Bad. I didn't realize that. Try
DATA:
Load
*,
Date(EXTENSION_START_DATE + DAYS) as EXTENSION_END_DATE
;
Load
*,
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0, Peek(EXTENSION_START_DATE) + DAYS + 1))) as EXTENSION_START_DATE
;
LOAD
ID,
ID_POS,
START_DATE,
END_DATE,
DAYS
FROM BD_Example.xlsx (ooxml, embedded labels, table is Example);
Thank you! It works now!