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?
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)...
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!
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)
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))