Qlik Sense unable to recognize number or date fields

[fishing_weights]

Background:

1. I'm on Qlik Sense Enterprise.
2. I am using Presto to load a date and number field stored as VARCHAR in my data base. Example of the data stored: 
3. In the Qlik Load Editor I've set my DateFormat to match the data: SET DateFormat ='YYYY-MM-DD'

The Issue:

They're loaded and stored as ascii or text even though the format matches.

The same issue for numbers too the field is being stored as ACII or text instead of integer or numeric.

I know i can use Date# or Num# to convert or cast in my SQL script. But I'm loading hundreds of table and that is not practical. 

Previously using QlikView and it could recognize  and interpret these fields correctly as number or date but not Qlik Sense.

Am I missing some variable or configuration? Thanks in advance!

[diagonjope]

Hi @fishing_weights,

Can you share what version of QSEoW are you using?  We have a customer that is migrating from QV and is experiencing the opposite results when reading NCHAR from ORACLE and the character sequence maps to a date format.  They want to keep them as text, but QSE keeps converting the field to a date.

Cheers,

++José

[chriscammers]

I've said for years that Qlik does not care about data types and what you said about QlikView vs Qlik Sense is not true, Qlik Sense has always converted data types however I do think something has changed on the database side with engines like presto working a bit differently than traditional SQL Server databases. Non of this helps to solve your problem so let's talk about that.

I must say you have not provided very much information about this problem except to say that it is annoying and you don't want to have to do anything about it so let's explore some solutions.

You said you are loading hundreds of tables so are you saving these tables to QVD files or using the data directly in the app where you are extracting them. If you have a transform process then you could convert the data types when you do your transform in which case you would not have to analyze hundreds of tables to figure out which text fields are actually text vs numbers or dates.

Another way you could deal with it would be to automate your data extraction process

I have some routines that I have written that take a list of tables, extract the columns from the database catalog and then I use a mapping table to substitute expressions. In your case you could create a map of the important date and number fields and inject sql or qlik expressions to change the data types.

I guess the level of effort you want to put into solving this depends on how much effort it will take to do it downstream. I do agree if you are extracting that many tables then you don't want to be doing it manually.

[fishing_weights]

I'm using the May 24 version

[fishing_weights]

Hi @chriscammers thanks for the thoughtful reply. some follow up questions

I am already setting the DateFormat ='YYYY-MM-DD'  which is the same format as the date data in server

for numeric there isn't a format to set.

1

Would you recommend some other connector or engine that would be the most compatible?

And I was told in an internal conversation that Presto does not use DSN and this is why the data is not being interpreted automatically is there some configuration that we can set instead?

---

@chriscammers wrote:

...I do think something has changed on the database side with engines like presto working a bit differently than traditional SQL Server databases....

---

2

Is there any guide I can read up on this transform process which would not require me to analyze each field to determine if it should be a numeric or date field?

I am extracting and storing them as QVD to be further manipulated in the data model. but that would also require me to Date# and Num# each field.... 

---

@chriscammers wrote:

...you are loading hundreds of tables so are you saving these tables to QVD files or using the data directly in the app where you are extracting them. If you have a transform process then you could convert the data types when you do your transform in which case you would not have to analyze hundreds of tables to figure out which text fields are actually text vs numbers or dates....

[chriscammers · Accepted solution]

1. I don't think the date format variable have anything to do with weather or not the engine will recognize text values as numeric or date. You will find at some points the automatic conversion to numbers from strings to be a little annoying too. Look for posts on numeric strings with leading zeros.
2. I'm not sure if there are other drivers out there for Presto
3. Regrettably, sometimes data management requires some tedious work. There are probably some scripts you could write to analyze your data and identify the date and numeric fields so you could do some further processing. Since you are describing your data as fairly large I would expect it to take some time to do that kind of profiling. If you have Qlik Saas you could use the data profiling features that are automatically available for all your qvd files.

[fishing_weights]

Thanks @chriscammers looks like I'll have to find some workarounds. One idea we found was to use Trim() that would convert dates and numbers accordingly. Perhaps I could write a script to trim all fields regardless. would need to check the impact...

[diagonjope]

@fishing_weights ,

I don't have proof, but I think your issue might be caused by changes in the metadata passed by the ODBC drivers to the Qlik loading engine. 

To load data in QVS, you were probably using the ODBC drivers provided by the data source vendor.  I assume that in Qlik Sense you are using the ODBC connector that comes bundled with the platform (sourced from SIMBA, I believe). 

Have you tried using the same ODBC drivers in both platforms?

Cheers,

++Jose