13 Replies Latest reply: Oct 10, 2016 11:42 AM by Sunny Talwar RSS

    Converting Dates to Month to Qtr. to Year

    Ankit Gupta

      Hi I have a column with Dates in my Data, I want further column from it which shows Month , Qtr, years. I am using below code:

       

      [DISP_DATE],

        Week ([DISP_DATE]) as [DISP_Week],

        Month([DISP_DATE]) as [DISP_Month],

        Year([DISP_DATE]) as [DISP_Year],

        'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

       

      I am able to compile code without an error , but when I try to use Week , Month , year column in my dashboard there is no value, can someone please help me in this regard, I am very new to Qlik and not sure what error I am making .

       

      Also, DISP_DATE columns have values like 01-Sep-2016 , 03-Sep-2016 .... 02-Oct-2016

        • Re: Converting Dates to Month to Qtr. to Year
          jagan mohan rao appala

          Hi,

          Try like this

           

          Data:

          LOAD

          *,

            Week ([DISP_DATE]) as [DISP_Week],

            Month([DISP_DATE]) as [DISP_Month],

            Year([DISP_DATE]) as [DISP_Year],

            'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];

          LOAD

          Date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],

          *

          FROM DataSource;

           

          Regards,

          Jagan.

            • Re: Converting Dates to Month to Qtr. to Year
              Ankit Gupta

              Hello Jagan, thanks for the guidance , I tried to put below code in my script, it changes the format of my data from 03-Sep-2016 to 03/08/2016 that is fine, but Week, Qtr and Year columns are still blank.


              Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

                //[DISP_DATE],

                   Week ([DISP_DATE]) as [DISP_Week],

                   Month([DISP_DATE]) as [DISP_Month],

                   Year([DISP_DATE]) as [DISP_Year],

                   'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

                • Re: Converting Dates to Month to Qtr. to Year
                  jagan mohan rao appala

                  Hi Ankit,

                  The script should work, did you noticed the second load statement(Preceding Loading) in my script.  I am not sure you did the preceding loading.  Please find below script highlighted in Bold text. 

                   

                  Data:

                  LOAD

                  *,

                    Week ([DISP_DATE]) as [DISP_Week],

                    Month([DISP_DATE]) as [DISP_Month],

                    Year([DISP_DATE]) as [DISP_Year],

                    'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];

                  LOAD

                  Date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],   // Formatting the date

                  *

                  FROM DataSource;

                   

                  Regards,

                  Jagan.

                    • Re: Converting Dates to Month to Qtr. to Year
                      Ankit Gupta

                      Hi Jagan, I am loading the data from single excel worksheet, pasting the entire script below for your reference so that you can see whats wrong I am doing

                       

                      Thx

                      Ankit

                       

                      [Export Worksheet]:

                      LOAD

                      [REGION_DESC],

                        [TERRITORY_DESC],

                        [MD_CUST_ID],

                        [MD_CMID],

                        //[MD_FIRST_NAME],

                        //[MD_LAST_NAME],

                          [MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],

                        [PATIENT_CMID],

                        [PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],

                        [ABC_ID],

                        [PATIENT_INITIALS],

                        [PATIENT_BIRTH_DATE],

                        [PATIENT_GENDER],

                        [PATIENT_DISPOSITION],

                        [REFERRAL_DATE],

                        [GRADUATION_DATE],

                        [ASAP_DISPOSITION],

                          //[DISP_DATE], [ This is the Original column from Excel]

                           Week ([DISP_DATE]) as [DISP_Week],

                           Month([DISP_DATE]) as [DISP_Month],

                           Year([DISP_DATE]) as [DISP_Year],

                           'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

                           Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

                          [DISP_AGE],

                        [DISP_REASON],

                        [SR_STATUS],

                        [SR_SOURCE],

                        [PROCUREMENT],

                        [AVONEX_SPP],

                        [DECISION_MAKER],

                        [TREATMENT_CHOICE],

                        [ABM_ALERT_CODE],

                        [MD_OPT_OUT],

                        [ACCESS_ENROLLED_FLAG],

                        [CURR_THERAPY],

                        [CURR_THERAPY_CONFIRM_DT],

                        [PREV_THERAPY],

                        [INS_PRVDR1_CMID],

                        [INS_PRVDR1_NAME],

                        [INS_PRVDR1_PHONE],

                        [INS_PRVDR2_CMID],

                        [INS_PRVDR2_NAME],

                        [INS_PRVDR2_PHONE],

                        [ENROLLED_ATS_FLAG],

                        [SHIP_DATE],

                        [SA_SR_ID],

                        [SOURCE_SR_ROW_ID],

                        [TRAINING_REQUIRED],

                        [TRAIN_BY_NURSE],

                        [REFUSED_TRAINING_FLG],

                        [COPAY_STATUS],

                        [ZIN_REMS_ID],

                        [PATIENT_REMS_STATUS],

                        [PATIENT_REMS_STATUS_DATE],

                        [MD_REMS_STATUS],

                        [MD_REMS_STATUS_DATE],

                        [JOIN_TERR],

                        [TERR_DESC]

                      FROM [lib://AttachedFiles/disp_export.xlsx]

                      (ooxml, embedded labels, table is [Export Worksheet]);

                        • Re: Converting Dates to Month to Qtr. to Year
                          jagan mohan rao appala

                          Hi,

                           

                          Try like this

                           

                          [Export Worksheet]:

                          LOAD

                          *,

                          Week ([DISP_DATE]) as [DISP_Week],

                               Month([DISP_DATE]) as [DISP_Month],

                               Year([DISP_DATE]) as [DISP_Year],

                               'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];

                          LOAD

                          [REGION_DESC],

                            [TERRITORY_DESC],

                            [MD_CUST_ID],

                            [MD_CMID],

                            //[MD_FIRST_NAME],

                            //[MD_LAST_NAME],

                              [MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],

                            [PATIENT_CMID],

                            [PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],

                            [ABC_ID],

                            [PATIENT_INITIALS],

                            [PATIENT_BIRTH_DATE],

                            [PATIENT_GENDER],

                            [PATIENT_DISPOSITION],

                            [REFERRAL_DATE],

                            [GRADUATION_DATE],

                            [ASAP_DISPOSITION],

                              //[DISP_DATE], [ This is the Original column from Excel]    

                               Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

                              [DISP_AGE],

                            [DISP_REASON],

                            [SR_STATUS],

                            [SR_SOURCE],

                            [PROCUREMENT],

                            [AVONEX_SPP],

                            [DECISION_MAKER],

                            [TREATMENT_CHOICE],

                            [ABM_ALERT_CODE],

                            [MD_OPT_OUT],

                            [ACCESS_ENROLLED_FLAG],

                            [CURR_THERAPY],

                            [CURR_THERAPY_CONFIRM_DT],

                            [PREV_THERAPY],

                            [INS_PRVDR1_CMID],

                            [INS_PRVDR1_NAME],

                            [INS_PRVDR1_PHONE],

                            [INS_PRVDR2_CMID],

                            [INS_PRVDR2_NAME],

                            [INS_PRVDR2_PHONE],

                            [ENROLLED_ATS_FLAG],

                            [SHIP_DATE],

                            [SA_SR_ID],

                            [SOURCE_SR_ROW_ID],

                            [TRAINING_REQUIRED],

                            [TRAIN_BY_NURSE],

                            [REFUSED_TRAINING_FLG],

                            [COPAY_STATUS],

                            [ZIN_REMS_ID],

                            [PATIENT_REMS_STATUS],

                            [PATIENT_REMS_STATUS_DATE],

                            [MD_REMS_STATUS],

                            [MD_REMS_STATUS_DATE],

                            [JOIN_TERR],

                            [TERR_DESC]

                          FROM [lib://AttachedFiles/disp_export.xlsx]

                          (ooxml, embedded labels, table is [Export Worksheet]);

                  • Re: Converting Dates to Month to Qtr. to Year
                    Sunny Talwar

                    Like Jagan pointed out, there seems to be interpretation problem. QlikView doesn't always understand your date. One way to handle this without using Date#() would be to define your date format in the environmental variable

                     

                    SET ThousandSep=' ';

                    SET DecimalSep=',';

                    SET MoneyThousandSep=' ';

                    SET MoneyDecimalSep=',';

                    SET MoneyFormat='# ##0,00 ₽;-# ##0,00 ₽';

                    SET TimeFormat='h:mm:ss';

                    SET DateFormat='DD-MMM-YYYY';

                    SET TimestampFormat='DD-MMM-YYYY h:mm:ss[.fff]';

                    SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';

                    SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';

                    SET LongMonthNames='Январь;Февраль;Март;Апрель;Май;Июнь;Июль;Август;Сентябрь;Октябрь;Ноябрь;Декабрь';

                    SET LongDayNames='понедельник;вторник;среда;четверг;пятница;суббота;воскресенье';

                    SET FirstWeekDay=0;

                    SET BrokenWeeks=1;

                    SET ReferenceDay=0;

                    SET FirstMonthOfYear=1;

                    SET CollationLocale='ru-RU';

                     

                    and by doing this, you would be able to avoid the use of preceding load and your current script should work

                     

                    [DISP_DATE],

                      Week ([DISP_DATE]) as [DISP_Week],

                      Month([DISP_DATE]) as [DISP_Month],

                      Year([DISP_DATE]) as [DISP_Year],

                      'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

                     

                    But many times we run into an issue where we pull data from multiple sources with different formats, that's when Date#() in handy and it makes sense to set the environmental variable to a format which you would like to see your date in the front end of the application.

                     

                    Read here to learn about Dates in QlikView

                    Why don’t my dates work?

                    Get the Dates Right

                    • Re: Converting Dates to Month to Qtr. to Year
                      Miskin Mazgaonkar

                      Hi,

                       

                      Try this one

                       

                      [Export Worksheet]:

                      LOAD

                      [REGION_DESC],

                        [TERRITORY_DESC],

                        [MD_CUST_ID],

                        [MD_CMID],

                        //[MD_FIRST_NAME],

                        //[MD_LAST_NAME],

                          [MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],

                        [PATIENT_CMID],

                        [PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],

                        [ABC_ID],

                        [PATIENT_INITIALS],

                        [PATIENT_BIRTH_DATE],

                        [PATIENT_GENDER],

                        [PATIENT_DISPOSITION],

                        [REFERRAL_DATE],

                        [GRADUATION_DATE],

                        [ASAP_DISPOSITION],

                          //[DISP_DATE], [ This is the Original column from Excel]

                           Week (date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Week],

                           Month(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Month],

                           Year(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Year],

                           'Q-' & Ceil(Month(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))/3) as [DISP_Quater],

                          date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],

                          [DISP_AGE],

                        [DISP_REASON],

                        [SR_STATUS],

                        [SR_SOURCE],

                        [PROCUREMENT],

                        [AVONEX_SPP],

                        [DECISION_MAKER],

                        [TREATMENT_CHOICE],

                        [ABM_ALERT_CODE],

                        [MD_OPT_OUT],

                        [ACCESS_ENROLLED_FLAG],

                        [CURR_THERAPY],

                        [CURR_THERAPY_CONFIRM_DT],

                        [PREV_THERAPY],

                        [INS_PRVDR1_CMID],

                        [INS_PRVDR1_NAME],

                        [INS_PRVDR1_PHONE],

                        [INS_PRVDR2_CMID],

                        [INS_PRVDR2_NAME],

                        [INS_PRVDR2_PHONE],

                        [ENROLLED_ATS_FLAG],

                        [SHIP_DATE],

                        [SA_SR_ID],

                        [SOURCE_SR_ROW_ID],

                        [TRAINING_REQUIRED],

                        [TRAIN_BY_NURSE],

                        [REFUSED_TRAINING_FLG],

                        [COPAY_STATUS],

                        [ZIN_REMS_ID],

                        [PATIENT_REMS_STATUS],

                        [PATIENT_REMS_STATUS_DATE],

                        [MD_REMS_STATUS],

                        [MD_REMS_STATUS_DATE],

                        [JOIN_TERR],

                        [TERR_DESC]

                      FROM [lib://AttachedFiles/disp_export.xlsx]

                      (ooxml, embedded labels, table is [Export Worksheet]);