Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Export to excel changes time value

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
2 Replies
loveisfail
Not applicable

Re: Export to excel changes time value

Can you share QVF file

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: Export to excel changes time value

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;