2 Replies Latest reply: Mar 19, 2017 4:17 PM by Blaine Hyde RSS

    Export to excel changes time value

    Blaine Hyde

      Hi.  I have one of several date/time field that is fine in the table view within Qlik sense but upon export to excel, the micro-seconds are actually different for one of the the date/time fields and ok for others.  It's not a formatting issue I don't believe as the values look different and it doesn't appear to be a rounding issue.

       

      In Qlik the fields are:

       

      Qlik print screen.png

       

       

       

      Excel comes out as (OperationTime_IR is what is changed).:

         

      Inspection_Review_Process_End_TimeInspection_Review_Process_End_Time_WeekOperationTime_IR
      02/23/2017 12:55:52.9130802/23/2017 12:51:58.5158
      02/23/2017 12:55:52.9130802/23/2017 12:55:43.5543
      02/23/2017 12:55:52.9130802/23/2017 12:55:44.5544
      02/23/2017 12:55:52.9130802/23/2017 12:55:44.5544
      02/23/2017 12:55:52.9130802/23/2017 12:55:53.5553
      02/23/2017 12:55:52.9130802/23/2017 12:55:53.5553
      02/23/2017 12:55:52.9130802/23/2017 12:55:54.5554
      02/23/2017 12:55:52.9130802/23/2017 12:55:54.5554
      02/23/2017 12:55:52.9130802/23/2017 12:55:55.5555
        • Re: Export to excel changes time value
          Anil Babu Samineni

          Can you share QVF file

          • Re: Export to excel changes time value
            Blaine Hyde

            The code is (exporting the QVF is too big or I'm doing something wrong):

             

            SET ThousandSep=',';

            SET DecimalSep='.';

            SET MoneyThousandSep=',';

            SET MoneyDecimalSep='.';

            SET MoneyFormat='$#,##0.00;($#,##0.00)';

            SET TimeFormat='h:mm:ss TT';

            SET DateFormat='M/D/YYYY';

            SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

            SET FirstWeekDay=6;

            SET BrokenWeeks=1;

            SET ReferenceDay=0;

            SET FirstMonthOfYear=1;

            SET CollationLocale='en-US';

            SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

            SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

            SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

            SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

             

             

             

             

            IR:

            LOAD

             

             

               

                Step_IR,

                Timestamp(OperationTime_IR,'MM/DD/YYYY hh:mm:ss') as OperationTime_IR,

                Year(OperationTime_IR)&'-'&month(OperationTime_IR) as Op_Year_Month,

                TaskID_IR,

                "Operation/TASKACTION_IR",

                Row_Counter_IR_new,

                   

                TaskID_IR&Row_Counter_IR_new as TaskID_and_Row_Counter_Tag,

             

             

               

                if (Step_IR='Task Complete', 1,0) as Task_Complete_Counter, if (Step_IR='Task Complete', Row_Counter_IR_new, 1) as Task_Complete_Row_Counter,

             

             

                if (((Step_IR='Insp - Inspection Report' or Step_IR='Insp - Inspection Review') and ("Operation/TASKACTION_IR"='Release' or "Operation/TASKACTION_IR"='Reschedule' or "Operation/TASKACTION_IR"='Route')), 1,0) as Insp_Review_Process_Complete_Counter,

                if (((Step_IR='Insp - Inspection Report' or Step_IR='Insp - Inspection Review') and ("Operation/TASKACTION_IR"='Release' or "Operation/TASKACTION_IR"='Reschedule' or "Operation/TASKACTION_IR"='Route')), Row_Counter_IR_new, 1) as Insp_Review_Process_Complete_Row_Counter

                 

            FROM [lib://data (smi-rps_a-mewatkins) (smi-rps_bhyde)/ImageRightMain.qvd]

            (qvd)

            Where ((Workflow_IR='Uniondale' or Workflow_IR='BA - Scottsdale' or Workflow_IR='Charlotte' or Workflow_IR='BA - Salt Lake City' or Workflow_IR='Imports' or Workflow_IR='xImports') and OperationTime_IR>42300);

             

             

             

             

            Summary_IR:

            Load

            TaskID_IR,

             

             

            if(sum(Insp_Review_Process_Complete_Counter)>0, firstsortedvalue(Timestamp(OperationTime_IR,'MM/DD/YYYY hh:mm:ss'), -Insp_Review_Process_Complete_Row_Counter), 'Non Insp Review Process Completion') as Inspection_Review_Process_End_Time,

            if(sum(Task_Complete_Counter)>0, firstsortedvalue(Timestamp(OperationTime_IR,'MM/DD/YYYY hh:mm:ss'), -Task_Complete_Row_Counter), 'Non-Complete Task') as Task_End_Time

             

             

            Resident IR

            Group by TaskID_IR;

             

             

             

             

             

             

            Left Join (IR)

            Load

              TaskID_IR,

                Task_End_Time as Task_End_Time2,

                if (len(week(Task_End_Time, 0))<2, '0'&week(Task_End_Time), week(Task_End_Time)) as Task_End_Time2_Week,

                Inspection_Review_Process_End_Time as Inspection_Review_Process_End_Time2,

                if(len(week(Inspection_Review_Process_End_Time,))<2, '0'&week(Inspection_Review_Process_End_Time),week(Inspection_Review_Process_End_Time)) as Inspection_Review_Process_End_Time_Week

            Resident Summary_IR;