Corruption Importing date format

[rakeshshah]

Hi All

I am having issues importing from a CSV file - when I use the date# and date functions on load it appears to be modifying the date's month.

First Image is the original file opened in Notepad++ - i have highlighted the bit in questions - clearly a February Month

Second image is the import line i have in Qlik

Third image is the resulting field in Qlik sheet - changed to December

if it helps I am running the latest version of Qlik Sense Desktop 3.2 SR2 (10.18.3)

Looking for help with what im doing wrong

Thanks

Rakesh

[tomasz_tru]

mm stands for minutes-thats why you get 12. Try MM for month mask.

Tomasz

[ahaahaaha]

Hi Rakesh,

Try

=TimeStamp(TimeStamp#(PurgeChar(@37, '"'), 'YYYY/MM/DD hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss') as Rebuilt

Regards,

Andrey

[OmarBenSalem · Accepted solution]

First of all, your date field is rather a timestamp not a date; second, 'mm' stands for minutes, MM stands for month.

With that being said, I'd say this expression will work:

timestamp(Timestamp#(YourField,'YYYY/MM/DD hh:mm:ss'),'YYYY/MM/DD hh;mm:ss') as DateField;

[ahaahaaha]

Hi Omar,

You are absolutely right. There's double quotes around the date, so I applied function PurgeChar() to get rid of them.

Regards,

Andrey

[OmarBenSalem]

Excellent ; what does the @37 stands for?

[ahaahaaha]

Yes, judging by the picture, which was presented by Rakesh.  On the picture there are double quotes. Perhaps they prevent getting the right result.

[rwunderlich]

The double quotes are not part of the data.  The quotes are field delimiters and will be removes automatically.

-Rob

[ahaahaaha]

Hi Rob,

Thank you very much for your comment. I just made my conclusion on the basis of experimental data (attached file).

Regards,

Andrey

[rakeshshah]

Reference is the column 37, the data source I have has no headers - the @37 (well from @1 to @38) was created by Qlik on importing data without headers from a CSV.