Data (Date information) Lost When Loading data from Excel

[Anonymous]

When I try to load data from an Excel File, I just find that some data are lost after loading the data. To be exact, some columns containing the date and time information cannot be selected into Qlik Sense and cause a lot of errors in the final visualizations.

As seen the two graphs from below, the first graph is the data loaded in Qlik Sense and the second is the original file in Excel. Obviously, the first and third line of Submission Time is not loaded into Qlik Sense. This will lead to a null value in the later charts created.

I am wondering if it is because of the date formats of excel and Qlik Sense are inconsistent. I am very confused about that. Can anyone tell me how to fix it? Many thanks!

[bwisealiahmad]

Hi,

Any way you can share the excel file with a couple rows or maybe the script and it would be much easier to help you. With that being said it doesn't look like it is reading these as dates...

Are you using the Data Manager for this or scripted way of loading it?

Best,

Ali A

[Anonymous]

Hi,

I load the data from Excel from Data Manager.

You mean the data was not loaded as Dates in Qlik Sense? Do you know how I can fix it?

Thanks!

[bwisealiahmad]

Hi,

In this YouTube video: https://www.youtube.com/watch?v=SCd-Tg-veAI

Go to 1:20 and notice "Date" column. There you see there is a date icon. Does your date fields have that?

Honestly, it would be easier if you could share an example file or something otherwise watch that video and hopefully that will help you.

Best,

Ali A

[Anonymous]

Hi,

Thanks for your following comment! I watched the YouTube tutorial video and check my date fields. By default, the three columns are selected as date formate by the system and it seems nothing wrong here.

And also I am really sorry that I cannot upload the file because it contains confidential information.

Another thing I just noticed is that in the two screenshots I posted in the question, you can find that in the third row first column of the second screenshot, the original date format is DD/MM/YYYY (12/10/2017), however, after I drag the file into Qlik Sense and load the data, it becomes 2017/12/10, which is in the form of YYYY/MM/DD. And clearly, this cell changes my date to December (originally it is October). Some similar errors also occur in my other dates.

I am still trying to figure it out.

Best

[bwisealiahmad]

Hi again,

No worries! Maybe you can only upload the columns with the date info and enter dummy values. It's hard to conceptually help when you can't replicate the error yourself and try to troubleshoot or see what it is doing for yourself.

I would do this the scripted way and use date interpret functions.

I hope you figure it out!

Best,

Ali A

Få Outlook for Android<https://aka.ms/ghei36>

[balabhaskarqlik]

Check this, to made changes in your script:

Date column is not loading as "Date &amp; Time"

[Anonymous]

Hi,

I just fixed the problem and it was due to the date format was incorrect when I loaded the data. And I changed the input Date Format under the Date Icon to be exactly the same as the format in the Excel file and all the data can be correctly loaded. Thanks for your tips!!

[Anonymous]

Hi,

I change the Date Icon in the Data Manager and it works now. Anyway thanks for your comment!