Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rakeshshah
New Contributor II

Corruption Importing date format

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

Image1.PNGimage2.PNGImage3.PNG

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Corruption Importing date format

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;

12 Replies
tomasz_tru
Valued Contributor

Re: Corruption Importing date format

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

Tomasz

ahaahaaha
Honored Contributor

Re: Corruption Importing date format

Hi Rakesh,

Try

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

Regards,

Andrey

OmarBenSalem
Esteemed Contributor

Re: Corruption Importing date format

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
Honored Contributor

Re: Corruption Importing date format

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
Esteemed Contributor

Re: Corruption Importing date format

Excellent ; what does the @37 stands for?

ahaahaaha
Honored Contributor

Re: Corruption Importing date format

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

Re: Corruption Importing date format

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

-Rob

ahaahaaha
Honored Contributor

Re: Corruption Importing date format

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
New Contributor II

Re: Corruption Importing date format

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.

Community Browser