cancel
Showing results for
Did you mean:
Creator III

## Date Calculation

Hello,

I have the following table with fields:

• ID: ID from Contract
• POS: Position Number
• START_DATE: Date Format DD/MM/YYYY
• END_DATE: Date Format DD/MM/YYYY
• DAYS: Num of Days to Sum Contract

IDID_POSSTART_DATEEND_DATEDAYS
COD00990024/2/201223/2/201518
COD00990124/2/201223/2/201518
COD00990224/2/201223/2/201512
COD00991023/5/201122/5/201512
COD00991123/5/201122/5/201512
COD00991223/5/201122/5/201512
COD00991323/5/201122/5/201512
COD00992017/3/201515/5/201518
COD00992117/3/201515/5/201518
COD00993023/3/201722/6/201812

What I need to get is something like this by script:

• EXTENSION_START_DATE: Date Format DD/MM/YYYY
• EXTENSION_END_DATE: Num of Days to Sum Contract

IDID_POSSTART_DATEEND_DATEDAYSEXTENSION_START_DATEEXTENSION_END_DATE
COD00990024/2/201223/2/20151824/2/201514/3/2015
COD00990124/2/201223/2/20151815/3/20152/4/2015
COD00990224/2/201223/2/2015123/4/201515/4/2015
COD00991023/5/201122/5/20151223/5/20154/6/2015
COD00991123/5/201122/5/2015125/6/201517/6/2015
COD00991223/5/201122/5/20151218/6/201530/6/2015
COD00991323/5/201122/5/2015121/7/201513/7/2015
COD00992017/3/201515/5/20151816/5/20153/6/2015
COD00992117/3/201515/5/2015184/6/201522/6/2015
COD00993023/3/201722/6/20181223/6/20185/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!!!

1 Solution

Accepted Solutions
Specialist

My Bad. I didn't realize that. Try

DATA:
*,
Date(EXTENSION_START_DATE + DAYSas EXTENSION_END_DATE
;
*,
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0, Peek(EXTENSION_START_DATE) + DAYS  + 1))) as EXTENSION_START_DATE
;
ID,
ID_POS,
START_DATE,
END_DATE,
DAYS
FROM BD_Example.xlsx (ooxml, embedded labels, table is Example);

6 Replies
Specialist

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

Creator III
Author

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:

IDID_POSSTART_DATEEND_DATEDAYS
COD00990024/2/201223/2/201518
COD00990124/2/201223/2/201518
COD00990224/2/201223/2/201512
COD00991023/5/201122/5/201512
COD00991123/5/201122/5/201512
COD00991223/5/201122/5/201512
COD00991323/5/201122/5/201512
COD00992017/3/201515/5/201518
COD00992117/3/201515/5/201518
COD00993023/3/201722/6/201812

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

Specialist

Yeah the above code still works.

Creator III
Author

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:

IDID_POSSTART_DATEEND_DATEDAYS
COD00990024/2/201223/2/201518
COD00990124/2/201223/2/201518
COD00990224/2/201223/2/201512
COD00991023/5/201122/5/201512
COD00991123/5/201122/5/201512
COD00991223/5/201122/5/201512
COD00991323/5/201122/5/201512
COD00992017/3/201515/5/201518
COD00992117/3/201515/5/201518
COD00993023/3/201722/6/201812
Specialist

My Bad. I didn't realize that. Try

DATA:
*,
Date(EXTENSION_START_DATE + DAYSas EXTENSION_END_DATE
;
*,
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0, Peek(EXTENSION_START_DATE) + DAYS  + 1))) as EXTENSION_START_DATE
;