17 Replies Latest reply: Oct 21, 2016 4:58 PM by David Bockol RSS

    Convert Date Format

    David Bockol

      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

          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
              David Bockol

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

                • Re: Convert Date Format
                  David Bockol

                  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

                    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
                        David Bockol

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

                         

                          • Re: Convert Date Format
                            Sunny Talwar

                            How about trying it out like this:

                             

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

                              • Re: Convert Date Format
                                David Bockol

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

                                  • Re: Convert Date Format
                                    David Bockol

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

                                      • Re: Convert Date Format
                                        Sunny Talwar

                                        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
                                            David Bockol

                                            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

                                                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);

                                                 

                                                Capture.PNG

                                                  • Re: Convert Date Format
                                                    David Bockol

                                                    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

                                                        This?

                                                         

                                                        Capture.PNG

                                                         

                                                        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);