Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
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
vamsee
Specialist
Specialist

My Bad. I didn't realize that. Try

DATA:
Load 
*,
Date(EXTENSION_START_DATE + DAYSas 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);

View solution in original post

6 Replies
vamsee
Specialist
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

microwin88x
Creator III
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

vamsee
Specialist
Specialist

Yeah the above code still works.

microwin88x
Creator III
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
vamsee
Specialist
Specialist

My Bad. I didn't realize that. Try

DATA:
Load 
*,
Date(EXTENSION_START_DATE + DAYSas 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);

microwin88x
Creator III
Creator III
Author

Thank you! It works now!