5 Replies Latest reply: Jul 3, 2017 3:33 AM by Nicolò Cogno RSS

    Qlil Sense Date Query

    Mohammed Al Radi

      Hi All,

       

      I have a query regarding how Qlik sense processes dates in excel and what I can do at the preparatory stage to ensure that certain fields can be transformed from 'general' to 'date' format when I click 'edit this table' and amend  a 'field menu' before loading data.

       

      The dates in my excel files all follow the DD/MM/YYYY format and I double check to see if the format in excel is 'date'. Sometimes I am lucky and I can convert my dates into 'date' format inside qlik before uploading data. This then allows me to analyse dates by day, month, quarter, monthyear, year etc...automatically. But in most cases, when I change the field format from general to date all the data disappears as shown below:

       

       

      When the field is still in 'General' format the data is in number form (42036 etc...)

      edit1.png

       

       

      When the field changed to 'date' format, the data disappears (keeping in mind that in the excel data source all dates are written dd/mm/yyyyy).

      edit2.jpg

       

      I realise there are Master calender functions that can be added to the data load editor after the data is uploaded, but I usually have problems inserting that script and if there was a way to allow me to change field format prior to uploading data (or at least know why sometimes i can change format from general to date and others times not) that would save a lot of time.

       

      Thank you very much for your help!

       

      Best,

       

      Mohammed

        • Re: Qlil Sense Date Query
          Nicolò Cogno

          Hi Mohammed,


          I think you should go to the script and change


                                        Date(Date#(DateField,'DD/MM/YYYY')) as DateField



          That will automatically force Qlik Sense to interpret your field as a date field every time you'll load new data.


          Let me know if I understood

            • Re: Qlil Sense Date Query
              Mohammed Al Radi

              Hi Nicolo,

               

              Thank you for reply! I tried you script but can't seem to get it working. Not sure if I am placing it in the wrong place or if I'm losing it from trying so many things! haha

               

               

              I have also tried the following which incorporates your expression:

               

              1)

              MinMax:

              LOAD Min(MonthStart(MonthYear)) AS MinDate,

                   Max(MonthStart(MonthYear)) AS MaxDate

              RESIDENT [TABLE NAME];

               

              LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

              LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

               

              MasterCalendar:

              LOAD Distinct

                Year(ReportDate) AS Year,

                Month(ReportDate) AS Month,

                  Date(ReportDate,'MM-YYYY') AS MonthYear;

                 

              LOAD

                Date(MonthStart($(vMinDate) + RecNo() - 1)) AS ReportDate

              AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

               

              DROP TABLE MinMax;

               

              Exit Script;

               

              2)

               

              LOAD [Table Name],

                   Date(MonthStart(Num#([Date Field])), 'MM/YYYY') as MonthYear,

              Year(num#(Date Field,'#')) as Year,

               

              Month(num#(Date Field,'#')) as Month

               

              Resident [Table Name];

               

              DROP Table [Table Name];

               

               

              and none of them worked or only partly worked. I realise I need to take a training on date functions though. The only place I can get your expression to work is as a master item in the UI. That allows me to view the information as DD/MM/YYYY then I need to put other expressions to view as MonthYear, Month, Year etc...

               

              But is there away of avoiding changing script in the data load editor or having to make master items by addressing the issue in the preparatory stage before loading data? As per the screen shots I attached you can change field type from general to date and sometimes Qlik recognises it and changes everything to date format (and auto generates a master calender function for you), and other times it just gives you blank cells like in the second picture.

               

              Best,

               

              Mohammed

                • Re: Qlil Sense Date Query
                  Nicolò Cogno

                  Hi Mohammed,

                   

                  could you provide us a sample of the result that you are getting with the above expressions?

                   

                  I saw you used Date(ReportDate,'MM-YYYY') AS MonthYear, if you try with Date(ReportDate, 'DD/MM/YYYY'), do you still have errors?

                                          

                   

                  Nicolò

                    • Re: Qlil Sense Date Query
                      Mohammed Al Radi

                      Hi Nicolo,

                       

                      Thank you for your support. In the end this worked:

                       

                      Temp:

                      Load num#([Date Field],'#') as NumericDate

                      RESIDENT [TABLE];

                       

                      MinMax:

                      LOAD Min(NumericDate) AS MinDate,

                         Max(NumericDate) AS MaxDate

                      RESIDENT Temp;

                       

                       

                      LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

                      LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

                       

                       

                      MasterCalendar:

                      LOAD Distinct

                      text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date Field],

                      Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

                      Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

                          ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,

                      Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear]

                      AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

                       

                       

                      DROP TABLE MinMax;

                      DROP TABLE Temp;