Dual statement where data is Julian Date vs today + number of days

[#1]

Hi Everyone,

I am trying to do the following:

if(YourDataField< Today() , Dual('Already Expired', 1),
if(YourDataField>= Today() and YourDataField<= Today()+ 30, Dual('Expires within a Month', 2),
if(YourDataField>= Today()+31 and YourDataField<= Today()+ 60, Dual('Expires within 2 Months', 3),
if(YourDataField>= Today()+61 and YourDataField<= Today()+ 90, Dual('Expires within 3 Months', 4),
if(YourDataFiel>= Today()+91 and YourDataField<= Today()+ 180, Dual('Expires between 3 and 6 Months', 5),
if(YourDataField>= Today()+181 and YourDataField<= Today()+ 270, Dual('Expires between 6 and 9 Months', 6),
if(YourDataField>= Today()+271 and YourDataField<= Today()+ 365, Dual('Expires between 9 Months and a Year', 7),
if(YourDataField>= Today()+451, Dual('Expires in more than 12 months', 8))))))))) as [Expiry Range],

The only two results of this Load is 'Already Expired' or 'Expires in more than 12 months'.

My Data Field is in a Julian Date Format. Is it possible it would be the Source of my problem?

If so, how can I go and fix it?

Please let me know,

Sam

[Gysbert_Wassenaar]

Yes. Qlikview uses the ISO 8601 standard for dates and times. The dates are using the Gregorian Calendar.

Maybe this discussion can help: JDE Date to Standard Date Format

[#3]

Ok thank you!

That being said, I did have ‘YourDataField’ transformed to a Gregorian date and named it ‘Gregdate’ in a Prior Line of my Script.

I even used the ‘Gregdate’ instead of the ‘YourDataField’. And it errors out at every loads…

Am I missing something?

Please let me know,

Thank you!

Have a great day!

Samuel Martel

Inventory Planning Coordinator

Edwards Lifesciences (Canada) Inc.

Tel number 905-819-6922

[#4 · Accepted solution]

The answer is simple enught. The actual Formula to change Julian date to greg date has to be in the load.

Basic Julian date to Greg date conversion Formula:

date(AddYears('1/1/1900',num(left(YourDataField,len(YourDataField)-3)))+num(right(YourDataField,3))-1)

Now within the initial Load:

         if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) < Today() , Dual('Already Expired', 1),   
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today() and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 30, Dual('Expires within a Month', 2),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+31 and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 60, Dual('Expires within 2 Months', 3),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+61 and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 90, Dual('Expires within 3 Months', 4),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+91 and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 180, Dual('Expires between 3 and 6 Months', 5),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+181 and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 270, Dual('Expires between 6 and 9 Months', 6),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+271 and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 365, Dual('Expires between 9 Months and a Year', 7),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+366 and  date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) <= Today()+ 450, Dual('Expires between 12 and 15 Months', 8),
           if(date(AddYears('1/1/1900',num(left(YOURDATAFIELD,len(YOURDATAFIELD)-3)))+num(right(YOURDATAFIELD,3))-1) >= Today()+451, Dual('Expires in more than 15 months', 9)))))))))) as [Expiry Range]

[Gysbert_Wassenaar]

That can be done a bit differently, so you need less calculations:

//preceding load

load

[Field A], [Field B], ...etc

if(YourDataField< Today() , Dual('Already Expired', 1),
if(YourDataField>= Today() and YourDataField<= Today()+ 30, Dual('Expires within a Month', 2),
if(YourDataField>= Today()+31 and YourDataField<= Today()+ 60, Dual('Expires within 2 Months', 3),
if(YourDataField>= Today()+61 and YourDataField<= Today()+ 90, Dual('Expires within 3 Months', 4),
if(YourDataFiel>= Today()+91 and YourDataField<= Today()+ 180, Dual('Expires between 3 and 6 Months', 5),
if(YourDataField>= Today()+181 and YourDataField<= Today()+ 270, Dual('Expires between 6 and 9 Months', 6),
if(YourDataField>= Today()+271 and YourDataField<= Today()+ 365, Dual('Expires between 9 Months and a Year', 7),
if(YourDataField>= Today()+451, Dual('Expires in more than 12 months', 8))))))))) as [Expiry Range];

//initial load

load

[Field A], [Field B], ...etc,

date(AddYears('1/1/1900',num(left(YourDataField,len(YourDataField)-3)))+num(right(YourDataField,3))-1)as YourDataField

From ...yoursource...;

The output of the initial load is piped into the preceding load where it's processed further.