topic Re: Convert Date Format in QlikView

Convert Date Format

Fri, 21 Oct 2016 15:12:08 GMT

I used the following script to convert the date format, but the result included some null values. How do I correct null values ?

Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate]

My ultimate objective is to subtract today's date - the original effective date, then divide by 365 to calculate the years insured as follows:

Floor(Today())-Floor(Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY')))/365 as Yrsinsured

The syntax in the latter script is incorrect, how do I fix it ?

Re: Convert Date Format

sunny_talwar — Fri, 21 Oct 2016 15:41:16 GMT

May be some of the [Original Eff Date] are not in YYYYMMDD format, is that true? If that's true, you need to use Alt function for QlikView to understand different type of date formats. If that above is true, can you tell us what all formats do you have for [Original Eff Date] field?

Re: Convert Date Format

Fri, 21 Oct 2016 15:43:32 GMT

The original effective date field is a number field that includes numbers in YYYYMMDD format without exception.

Re: Convert Date Format

Fri, 21 Oct 2016 15:46:42 GMT

My ultimate objective is to subtract today's date - the original effective date, then divide by 365 to calculate the years insured as follows:

Floor(Today())-Floor(Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY')))/365 as Yrsinsured

Re: Convert Date Format

sunny_talwar — Fri, 21 Oct 2016 15:47:46 GMT

If that is true, than I don't see any reason why there should be any nulls. Can you may be do a side by side comparison of each of row to check which values turn to be nulls?

LOAD Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate],

[Original Eff Date]

and then create a table box object to see all those places where Orgeffdate is null.

Re: Convert Date Format

Fri, 21 Oct 2016 15:52:51 GMT

I had already done this, but still could not explain null values.

Re: Convert Date Format

sunny_talwar — Fri, 21 Oct 2016 16:02:12 GMT

How about trying it out like this:

Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate],

Re: Convert Date Format

Fri, 21 Oct 2016 16:04:42 GMT

I copied your script, ran it, got syntax error.

Re: Convert Date Format

Fri, 21 Oct 2016 16:08:18 GMT

I reran your script with the comma at end, script ran fine, but still have null values.

Re: Convert Date Format

sunny_talwar — Fri, 21 Oct 2016 16:11:40 GMT

Would you be able to share few of the dates from the sample above as text and I will try it out at my end.

Best,

Sunny

Re: Convert Date Format

Fri, 21 Oct 2016 16:22:19 GMT

I've identified the issue, but still not sure how to resolve. The script loads two excel files:T1_Active and T2_Term with identical field names. The script calculates date for T1 but there is no script for T2 date, it is null simply because it was never calculated. Script editor won't allow me to enter same date calcualtion for T2 date. My ultimate goal is to calculate the following for T1 and T2.

(Floor(Today())-Floor(Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY')))/365 as Yrsinsured

I've attached qvw file, T1,T2.

Re: Convert Date Format

sunny_talwar — Fri, 21 Oct 2016 17:10:45 GMT

May be add the same two the second table, right?

T1_Active:

LOAD [Entity Status],

[Policy Type],

[Profession Flag],

[Customer No],

[Account No],

[Original Eff Date],

[Policy Eff Date],

[Termin Date],

[Student Flag],

[Source Code],

[State Code],

[Base Premium],

Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate]

FROM

Active.xlsx

(ooxml, embedded labels, table is Active);

NoConcatenate

T2_Term:

LOAD [Entity Status],

[Policy Type],

[Profession Flag],

[Customer No],

[Account No],

[Original Eff Date],

[Policy Eff Date],

[Termin Date],

[Student Flag],

[Source Code],

[State Code],

[Base Premium],

Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate]

FROM

Term.xlsx

(ooxml, embedded labels, table is Term);

Re: Convert Date Format

Fri, 21 Oct 2016 20:36:24 GMT

You fixed the null value issue but I still need to calculate years insured, as mentioned in my earlier replies. You’ve been so helpful that I feel bad asking for additional help. Should I ask for your help with the years insured calculation ?

Re: Convert Date Format

sunny_talwar — Fri, 21 Oct 2016 20:51:28 GMT

This?

T1_Active:

LOAD [Entity Status],

[Policy Type],

[Profession Flag],

[Customer No],

[Account No],

[Original Eff Date],

[Policy Eff Date],

[Termin Date],

[Student Flag],

[Source Code],

[State Code],

[Base Premium],

Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate],

(Floor(Today())-Floor(Date#([Original Eff Date],'YYYYMMDD')))/365 as Yrsinsured

FROM

Active.xlsx

(ooxml, embedded labels, table is Active);

NoConcatenate

T2_Term:

LOAD [Entity Status],

[Policy Type],

[Profession Flag],

[Customer No],

[Account No],

[Original Eff Date],

[Policy Eff Date],

[Termin Date],

[Student Flag],

[Source Code],

[State Code],

[Base Premium],

Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate],

(Floor(Today())-Floor(Date#([Original Eff Date],'YYYYMMDD')))/365 as Yrsinsured

FROM

Term.xlsx

(ooxml, embedded labels, table is Term);

Re: Convert Date Format

Fri, 21 Oct 2016 20:58:17 GMT

You did it. I had already run similar script, which may not have worked because my software version is a free version which expired. I just installed the licensed version about 10 minutes ago and the script works great.

THANKS !! Your techinical support is outstanding.

Re: Convert Date Format

Fri, 21 Oct 2016 21:15:04 GMT

My script was simalar but incorrect, so I get no credit for resolving this, you get all the credit. THANKS AGAIN !!