Dates and Data Model Viewer

[colinodonnel]

Have a bit of a curious case here.

Using Qlik Sense Desktop April 2018.

System variables are:

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

I am loading dates from a CSV file via the Load Editor.

When loaded raw "as is", in the data model viewer they are in the format YYYY-MM-DD

The following tags have been added $number $integer $timestamp $date

So I want the dates in the same format as the System variables.

As part of my data transformation strategy I leave the original field "raw" and the following formula works:

Load

DateField,

Date (DateField) as DateField1   //this changes the date format from YYYY-MM-DD to DD/MM/YYYY which is what I want

So next, I then comment out DateField,  remove the 1 from DateField1 and reload i.e.

Load

//DateField,

Date (DateField) as DateField

But now, DateField is back to the wrong format i.e. the format it was in when it first loaded.

For some reason, no matter what I try when the field name remains the same, it always loads back to the original format.

s a work around, I leave the '1' in place and rename the field at the end using:

Rename field DateField1 to DateField;

ts almost like QS has decided that the date format should remain in the same format as the original load.

Any yes, I have tried turning it off and on again

Thanks,

[OmarBenSalem]

You'll encounter such a thing if u don't change its name because of the dual() functionning in Qlik.

What if u try :

date(date#(DateFiedl,'YYYY-MM-DD')) as DateField

[colinodonnel]

So is the dual function preventing me from changing the Datefield format?

Why is that?

I have tried as you suggested but I still get the format 'YYYY-MM-DD':

date(date#(DateField,'YYYY-MM-DD')) as DateField

Its only when I change the name of DateField does it change format.

[OmarBenSalem]

I really do not have the explanation for it;, but I know it has sthing to do with the dual nature of the Dates in Qlik.

Maybe stalwar1‌ could help?

[colinodonnel]

Ok, thanks Omar,

My understanding of Dual is there is:

1 – a version we see i.e. the String

2 – the numerical version in the System

On that basis, part 2 works fine.

But part 1 seems to be stuck on the format from the first load

[stigchel]

I'm not sure, but maybe it's because the format for the Datefield field is already stored from the first load and reverts to that. What if you force it to be your format ?

date(date#(DateField,'YYYY-MM-DD'),'DD/MM/YYYY') as DateField

[colinodonnel]

Thanks Piet,

But still the same behaviour.

It stays in the same format as the original load for that field name of 'YYYY-MM-DD'

[marcus_sommer · Accepted solution]

I think the reason is like Omar hinted the dual-nature of date-values which lead to the fact that your second load of the DateField didn't change the DateField from the first load - the numeric values behind the dates are the same and therefore no value will be stored and this means that also the string-representation (which contained the formatting) isn't touched.

The background to this is that Qlik doesn't store the tables like you load them (and how it the most other tools like SQL do) else they will be stored as symbol-tables (for each field one with only distinct field-values) and data-tables which contain only a pointer to the symbol-tables.

So if you really want to apply such logic of double-loading a field it should be rather in this way:

Load

DateField as DateFieldOrigin,

...

Load

Date (DateFieldOrigin) as DateField,

...

but if you don't want to keep the origin field I wouldn't use a two-step approach else just doing it within a single-step.

- Marcus

[colinodonnel]

Hi Marcus,

Yes I think I will have to tack the approach as you have suggested.

It's not ideal as I often don't know if I need to format a field or not.

Perhaps I can preview in the Folder Connection so see what the data visually appears to be.

Thanks,

Colin