Date-Misinterpretation when using a WHERE clause

[danielf_]

Hello all,

I have a similar problem as in the discussion below:

Date format changes when using where clasue on non-date field

I'm loading from an Oracle Database, using a Delta-Scenario in order to shorten the loading time.

When performing a Full-Load, everything is fine.

When performing a Delta-Load, QlikView interprets a number (e.g. 39689) of one of the fields as a date (e.g. 8/29/2008).

Full- and Delta-Load are exactly identical, with the exception that the Delta-Load has a WHERE-clause which filters on a specific date (necessary for the Delta-Scenario).

The problem is that I cannot simply perform a date- or num-cast (as described in the discussion above), because the corresponding field is a free-text-field. It contains texts, as well as dates in several formats (DD-MM-YYYY, M/D/YYYY, ...) as well as numbers in several formats (33.1; 33,1; ...). Even worse, sometimes one record contains multiple entries separated by a comma (e.g. 101,102), which sometimes means "OnehundredthousandOnehundredTwo" and sometimes "Onehundredone, Onehundredtwo".

In short, there are no rules behind how data is entered, therefore each format is possible and therefore a simple date- or num-cast does not work.

Nevertheless, I cannot simply cast as text, because then the results cannot be used in further steps anymore.

My question: Do you know why QlikView changes the interpretation of a value depending on the usage of a WHERE clause?

And what can I do in this specific case to work around this behavior?

Thanks a lot and best regards,

Daniel

[jonathandienst]

Since the field is a free text field, I assume that you do not want to apply any number conversion. Load it like this (on the first load from the database):

     LOAD Text(field1) As field1,

          ...;

     SQL SELECT field1, .....;

[Gysbert_Wassenaar]

When performing a Delta-Load, QlikView interprets a number (e.g. 39689) of one of the fields as a date (e.g. 8/29/2008).

Why do you think Qlikview interprets 39689 as a date? Dates are just numbers. Any formatting to make that number show as a date is just that, formatting.

[jonathandienst]

The reason that the WHERE works differently is probably because the WHERE returns different values for the first row (or first few rows). Qlikview uses the content of the first rows to try to sense the data type to automatically detect dates / currencies etc. The first row(s) of data from you "where" query is causing QV to think that it should be a date.

[swuehl · Accepted solution]

My first idea was also to use Text() function in the load.

I can't really believe that your sole issue is the misinterpretation of a value when using a WHERE clause. Do you really have absolutely no issues with a full load?

If you can't use a record specific casting nor a static cast to text values, I assume you are relying totally on QV's automatic interpretation. Since your fomats are not defined ('each format is possible'), I can't believe that this really works.

Or do you have any information at hand to validate / correct the values or their formats in a subsequent step?

Note also that for any given field, each number can only have one textual representation, so you can't  have a number 42422 represented as '42422' and a date '2016-02-22' with the underlying same numeric value (but you can still reformat the value's text representation per context, using num() or date() )

[danielf_]

Thank you all for your answers.

Swuehl, you are correct, this is the reason.

The WHERE clause was not the issue, but that each number has only one textual representation, depending which representation was read in at first.

I now load the field twice, once as a text()-cast and once without any casts.

For table views, I then use the text()-casted value and for charts the normal one, as it should not matter in a chart how the numeric value is represented.

Thanks again for your support!