Add Data imports dates incorrectly...

[#1]

Hi...

I'm very new to Qlik Sense, testing it out to see if it would meet our needs--visualizing raw data files before they get loaded into our DW.  I have been banging my head trying to figure out why when I "Add Data" (via Data Manager) to an already existing table, all date fields in Qlik Sense are messed up in the 2nd file.

So, I have 2 incoming files, same schema in both (pipe delimited w/header row).  Dates come in as YYYYMMDD.  Qlik Sense imports the first file correctly and sets the dates accordingly.  When I "Add Data" & upload the 2nd file, all the dates import incorrectly, creating 5-digit or 6-digit years...

Each file contains a "run date" column, which contains the same date for every row in that file.  So, here's how they import...

• File1 run date value: '20161124' becomes '11/24/2016' (good)
• Add Data: File2 run date value: '20161117' becomes '3/29/57099' (bad)

This problem is across the board for all date fields in File2 (which all use YYYYMMDD), and I couldn't find anyone else experiencing a similar problem...

Thanks!

[robert_mika]

try:

DATE(DATE#(yourfield,'YYYYMMDD'),'MM/DD/YYYY') AS Date

[#3]

Hi Robert...

I'm a little confused where I should do this.  The original import read File1 correctly as YYYYMMDD, but Add Data for File2 didn't.  (Did it interpret those as 20161117 days past 1/1/1900?)  Therefore, my end result is this mix of '11/24/2016' (File1) and '3/29/57099' (File2) for this field.

So, are you saying this code above should be added to the import, but only for File2? ***  Or some other place (e.g. a funcion on a field in a chart/table)--therefore changing the date on the fly?  If it's the latter, how do I apply the code only for those values that are problematic?

*** I'm curious to know if there's an inconsistency between how dates are determined in Qlik Sense of initial imports vs. Add Data?

[robert_mika]

Are both of those files coming from the same source?

You need to add this part at the script level:

LOAD

Field1,

DATE(DATE#(yourfield,'YYYYMMDD'),'MM/DD/YYYY') AS Date

FROM...

If only File2causee the problem add only into this File.

[vlad_komarov]

I would not trust Qlik to convert YYYYMMDD string to a Date format directly.

I am usually splitting the string into YYYY, MM, and DD substrings and building a date field using MakeDate() function:

MakeDate(num#(left("TXN_DT", 4)), num#(mid("TXN_DT", 5, 2)), num#(right("TXN_DT", 2))) as [Trans Date]

Works every time!

Regards,

Vlad

[#6]

So, just so I understand...  When I bring it in via Add Data (using the wizard-like interface, set the delimiter, then "Load data and finish"), Qlik Sense properly sees the YYYYMMDD fields in the 1st file properly, and sets them as dates.

However, despite having an identical schema (including the same delimiter, field names, field order, and YYYYMMDD date format), I need to use a script to append the 2nd file into the dataset--manually setting each YYYYMMDD date field, in order to make sure that 2nd file gets loaded in the same way?

Seems like a bug to me in Add Data, where the code sees dates the first time around yet sees them as integers to be added to a date like 1/1/1900 on subsequent data appends (despite the target field already existing as a date)...

[Daniel_Moore]

You can modify fields with the Data manager (Add Data / Wizard) in multiple ways - two alternative ways I'd like to highlight:

Alternative one change the type to date:

Navigate -> Data manager -> Tables -> edit (pencil) Orders -> Bracket Box -> Date -> Input format: MMDDYYYY (or whatever your format is)

Alternative 2:

Make a new Calculated field with a date building formula

Navigate -> Data manager -> Tables -> edit (pencil) Orders -> Add field -> Calculated field -> Name-> OrdDate -> Expression to build the date:

MakeDate(Right(OrderDate,4) , Left(OrderDate,2) , Mid(OrderDate,3,2))