14 Replies Latest reply: Feb 21, 2018 8:50 AM by Michael Anywar RSS

    Date Function

    Michael Anywar

      Dear, i need help here.

      I want to load my data that includes date and timestamps.

      When I do it on cloud, its fine. but on my desktop app it refuses.

       

      My field ts is a time stamp. On my Qlik Sense cloud, it loads,

      However on the Destop it is shown as General, hence dates are shown as a string.

      If I use the Date function as you can see below, it does not load at all. the table field ts is left blank/ null.

      Help please.

       

      [TaskActivities]:

      LOAD

      // [ts] AS [ts],

      //    Date#(`ts`,'DD.MM.YYYY') as "ts",

          Date#(`ts`,'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

      [fullName] AS [fullName],

      [activityType] AS [activityType],

      [taskType] AS [taskType],

      [manufacturingOrderId] AS [manufacturingOrderId],

      [productId] AS [productId],

      [taskId] AS [taskId]

      RESIDENT RestConnectorMasterTable;

       

      Thank you.

        • Re: Date Function
          Youssef Belloum

          EDITED

           

          Hi,

           

          try this:

           

          [TaskActivities]:

          LOAD

          // [ts] AS [ts],


          Date(Date#(`ts`,'DD.MM.YYYY'),'DD.MM.YYYY') as "date",

          timestamp(timestamp#(`ts`,'DD.MM.YYYY hh:mm:ss[.fff]'),'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

           

          [fullName] AS [fullName],

          [activityType] AS [activityType],

          [taskType] AS [taskType],

          [manufacturingOrderId] AS [manufacturingOrderId],

          [productId] AS [productId],

          [taskId] AS [taskId]

          RESIDENT RestConnectorMasterTable;

            • Re: Date Function
              omar bensalem

              You can't do that Youssef.

               

              If ts field is written as follow: 'DD.MM.YYYY hh:mm:ss[.fff]'


              u can't do this:

              Date#(`ts`,'DD.MM.YYYY')


              Qlik won't recognize it; since it's not written as follow.

              and visversa

               

              see this example :

               

              load *,

              date(Date#(ts,'DD/MM/YYYY')) as date,

              timestamp(Timestamp#(ts,'DD/MM/YYYY hh:mm:ss')) as timestamp

              Inline [

              ts

              20/02/2018 11:45:36

              ];

              result:

              Capture.PNG

               

              Furthermore, what's the point of the 2 quotes surrounding the ts field?

              I mean this :

               

              timestamp(timestamp#(`ts`,'DD.MM.YYYY hh:mm:ss[.fff]'),'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",

              u'll have to eliminate those quotes for this to work

                • Re: Date Function
                  Youssef Belloum

                  you're right, i didn't pay enough attention of the format of the original field..

                   

                  you can use one of them depending on the original Ts format.

                   

                  if ts is a date:

                  Date(Date#(ts,'DD.MM.YYYY'),'DD.MM.YYYY') as "date",


                  if ts is a timestamp;

                  timestamp(timestamp#(ts,'DD.MM.YYYY hh:mm:ss[.fff]'),'DD.MM.YYYY hh:mm:ss[.fff]') as "ts",


                  of course remove that ' ' when calling the field on the function.

                • Re: Date Function
                  Michael Anywar

                  Is there something am doing wrong, or because am just new to this..?

                   

                  I below is the whole script  including screen shot am using, but still no success

                  Thank you all for your guidance

                  ..............................................................................................................................

                  ...............................................................................................................................

                  LIB CONNECT TO 'Task Activities';

                   

                  // Action required: Implement the logic to retrieve the total records from the REST source and assign to the 'total' local variable.

                  Let total = 0;

                  Let totalfetched = 0;

                  Let startAt = 0;

                  Let pageSize = 100;

                   

                  for startAt = 0 to total step pageSize

                  RestConnectorMasterTable:

                   

                  SQL SELECT

                  "ts",

                  "fullName",

                  "activityType",

                  "taskType",

                  "manufacturingOrderId",

                  "productId",

                  "taskId"

                  FROM JSON (wrap on) "root"

                  WITH CONNECTION(Url%%#######");

                  // Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source.

                  // Please see the documentation for "Loading paged data."

                   

                  NEXT startAt;

                   

                  [TaskActivities]:

                  LOAD

                  // [ts] AS [ts],

                  //    Date(Date#(ts,'DD.MM.YYYY')) as "ts",

                      timestamp(timestamp#(ts,'DD.MM.YYYY hh:mm:ss[.fff]')) as "ts",

                  [fullName] AS [fullName],

                  [activityType] AS [activityType],

                  [taskType] AS [taskType],

                  [manufacturingOrderId] AS [manufacturingOrderId],

                  [productId] AS [productId],

                  [taskId] AS [taskId]

                  RESIDENT RestConnectorMasterTable;

                   

                  TaskId.png

                    • Re: Date Function
                      Gerold Roser

                      have you the  Settings correctly ?

                       

                       

                       

                      SET ThousandSep='.';
                      SET DecimalSep=',';
                      SET MoneyThousandSep='.';
                      SET MoneyDecimalSep=',';
                      SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
                      SET TimeFormat='hh:mm:ss';
                      SET DateFormat='DD.MM.YYYY';
                      SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
                      SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
                      SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

                        • Re: Date Function
                          Michael Anywar

                          Hello Gerold, this is what I have, and I believe its correct.

                           

                          SET ThousandSep=' ';

                          SET DecimalSep=',';

                          SET MoneyThousandSep=' ';

                          SET MoneyDecimalSep=',';

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

                          SET TimeFormat='hh:mm:ss';

                          SET DateFormat='DD.MM.YYYY';

                          SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

                          SET FirstWeekDay=0;

                          SET BrokenWeeks=0;

                          SET ReferenceDay=4;

                          SET FirstMonthOfYear=1;

                          SET CollationLocale='et-EE';

                          SET CreateSearchIndexOnReload=1;

                          SET MonthNames='jaan;veebr;märts;apr;mai;juuni;juuli;aug;sept;okt;nov;dets';

                          SET LongMonthNames='jaanuar;veebruar;märts;aprill;mai;juuni;juuli;august;september;oktoober;november;detsember';

                          SET DayNames='E;T;K;N;R;L;P';

                          SET LongDayNames='esmaspäev;teisipäev;kolmapäev;neljapäev;reede;laupäev;pühapäev';

                    • Re: Date Function
                      omar bensalem

                      can u share ur ts field without any tranformation? what is it returning?

                      • Re: Date Function
                        Gerold Roser

                        TimeStamp is not so esay

                         

                        // myDatum  20180122    (YYYYMMDD)

                        // myZEIT 062159        (hhmmss)

                        timestamp(Timestamp# myDatum & myZEIT   ,'YYYYMMDDhhmmss' ),'DD.MM.YYYY hh:mm:ss')             as myTimestamp          // (as Num)

                        Text(timestamp(Timestamp# myDatum & myZEIT   ,'YYYYMMDDhhmmss' ),'DD.MM.YYYY hh:mm:ss'))  as myTextTimestamp //  (as Text)

                        • Re: Date Function
                          Michael Anywar

                          This is the what save me.

                          Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [ts],

                          Finals.png

                          See the column root.ts

                          LOAD

                          Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [ts],

                          [fullName] AS [fullName],

                          [activityType] AS [activityType],

                          [taskType] AS [taskType],

                          [manufacturingOrderId] AS [manufacturingOrderId],

                          [productId] AS [productId],

                          [taskId] AS [taskId]