Strip all formats from data

[hobanwashburne]

I am having trouble combining data from multiple data sources where fields are sometimes stored as a Date and other times an integer. Is there a way to remove all formats from incoming data? I have tried splitting the data and into day/month/year and then using the MakeDate command but depending upon how the DateFomat is set QV doesn't recognize some of the dates.

Specifically, when set to:

SET DateFormat='M/D/YYYY';  the field appears null

SET DateFormat='YYYYMMDD';  appears correctly

[sunny_talwar]

When you have different formats, use Date#() function for QlikView to help understand the date formats

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

[Peter_Cammaert]

No there is no method to strip "all formats" from all source data. Simply because for some types of source data (e.g. text files or csv files) that's all you will get. Just like what Sunny already pointed out, you'll have to inspect individual fields from individual sources, make a quick analysis and decide on how to interprete and format these streams in QlikView.

Do you have many different types of data streams? I'm asking this because for example data from RDBMS can be formatted in the SQL query, even before it reaches QlikView.

[hobanwashburne]

This has been an ongoing problem. Some data was pulled via ODBC from an AS/400 system in the format 'YYYYMMDD' as an integer (3/6/17 =  20,170,306), then using Addmonths(MakeDate(num(left(datefield, 4), num(mid(datefield,2,2), num(right(datefield,2)),-3) converted into a recognized date in the fiscal calendar. Other data is coming from a SQL source Addmonths(datefield2, -3). Finally some dates were changed to drop the days via Date#(datefield, 'YYYYMM'). Also used are Month(datefield) and Year(datefield).

When loading the script everything appears consistent in the selection boxes. However, if I store the data in a QVD and read it back in to the exact same document without any changes. Qlikview will somehow treat the two date formats differently.

[Peter_Cammaert · Accepted solution]

Keep in mind that in the QlikView data treatment process (the T in ETL) there are actually two important phases: trying to get a clue about what data you get from your source(s) (using fuinctions like Date#, Time# and Timestamp#) and trying to get that data in a proper shape for output in QlikView (functions Month, Year, Date and Time come to mind). Inbetween lies a very important state: the proper internal representation of Date values in QlikView. If you get & keep all your date fields in the correct internal representation, you won't have any problems massaging them into the proper format for output.

For example, if your AS400 dates are always delivered as integers, a simple Date(Date#(AS400DateField, 'YYYYMMDD')) will convert them from the AS400 format into a QlikView binary representation and attach the default Date format to this value for future display. Date values are internally kept as dual values: a binary value for arithmetic purposes, and a display string/format for output purposes. The Xxxx#()-functions convert and store the first value, the Xxxx() functions set the string vallue (without changing the binary value). How to inspect dual values? Use num() to extract the binary value, and text() to extract the string representation.

QVDs store dual values. If the internal representation is right from the start, the QVD values will be correct and the values can be correctly read back into any QlikView document at a later time.