Data Recognition Problem

[Waïl_Riachi_Sanofi]

Hello All,

Here is a pdf attachment explaining my problem.

Thanks in advance for your help.

Waïl Riachi

[Andrei_Cusnir]

I took a look at the shared .PDF and there are a lot of information there. If my understanding is correct the overall use case scenario is:

1. You are using Qlik Sense to load data with Data manager
2. You would like to avoid using Data Load Editor, unless it is the only solution to the issue
3. You are loading many fields that contain dates from a GDMS database
4. Some fields are recognized as dates and thus have the calendar icon, but others don't
5. The ones that are not interpreted properly as dates, display the value 1/1/0001
6. You have consulted with the administrator of the database and he has ensured you that the dates are correct in the database.

From the information that you have provided it looks that the issue is most probably coming from the dataset and I suspect that it could be related to the difference in formatting. Qlik Sense is using the same interpretation for the loaded values, so if the values provided are indeed the same then there shouldn't be any issue, especially if the reload is being done in exactly the same Qlik Sense software (where the version is the same, environment is the same etc.)

First we should investigate the values from the data source, before they are loaded into Qlik Sense, and then check why they are interpreted differently. Because the issue could be with the format of the value, the connector that is retrieving the data from the database, the Qlik Sense interpretation etc. 

Can you please provide the following information?

1. What version of Qlik Sense are you using?
2. How do you establish the connection to the database? (Which connector are you using?)
3. Was it working for you before and suddenly stopped?
4. Did you do any changes to your environment? (Upgrade etc.)
5. Can you show how the data looks like inside the database?
6. Are you loading the data from various tables or all the fields are loaded from the same table?

For the number 5, please avoid sharing any sensitive data. It will be very helpful to see how the data looks like in the database, but it is not required to see the entire dataset. For example you can share a screenshot like that:

This way, we can clearly see how the values look like inside the data source, but as data it is not exposing any sensitive details. But this screenshot should be coming directly from the database, because we first need to see how the data looks like before arriving to Qlik Sense.

[Waïl_Riachi_Sanofi]

Hi @Andrei_Cusnir,

1- AMER OnPrem PTP with QlikSense April 2020 version

2- I use a direct connection to a database in "Data Connection" in ODBC (see attached screenshot)

3- the date is well recognized when I import excel extractions I have never had a problem but with the connection to the database there are date recognition problems (probably because there is more date and these famous "NULL" dates which are translated into 1/1/0001

4- We will implement August 2021 version.

5- 

6 - the data come from 2 different tables of the database which are linked together

Thank you in advance,

Regards,

Waïl Riachi

[Andrei_Cusnir]

Hello,

I was taking a look at the new information that you have provided. Here are my current assumptions and observations:

• The format of your data in the database looks like: DD/MM/YY HH:MM:SS. Ideally this shouldn't raise an issue, however I tried emulating your dataset and supplied 2 fields where filed one had dates values in the format DD/MM/YY HH:MM:SS (As you have) and the other field had dates in the format MM/DD/YYYY HH:MM:SS. Then I have created a Table chart where I added my 2 fields and I have then added 2 measure Expressions =MonthName(...). When passing the field with the date format as you have, the expression was failing and the result was Null, however when passing the other field the result was as expected. This makes me believe that the formatting you have for your dates in the data source, might not be properly recognized as dates, but rather as strings. Or this could be happening randomly for each value as during the data loading process Qlik tries to automatically recognize the value's format. If the value is not recognized as number, date etc., then Qlik interprets it as text.
• You have specified that you were able to load the same dates from Excel file and they were recognized properly. In this use case scenario, I believe that excel has converted the values to dates and when you have imported the file to Qlik Sense, the dates got recognized properly.

Next steps moving forward:

• You have mentioned that you are going to upgrade to version August 2021. I suggest that you do that as well, since this version have many improvements and new features. This might as well help you resolve the issue that you are facing. In that case please let us know, so that other community members will be aware of the solution as well.
• I know that you would like to avoid using Data Load Editor to load your data, however I believe that it will be very helpful for your use case scenario. It will allow you to format the date properly, to check if it is Null and thus return something different, if the value is not recognized as date then you can use a combination of various functions to fix that.

Workaround:

If you don't want to use Data Load Editor in your original application, then you can do a workaround:

• Create a new application
• Load the data with Data Load Editor 
• Format the dates properly in the script by using functions
• Test the data in the sheet that it is loaded properly and the dates are recognized
• Go back to the Date Load Editor and export the table into QVD file
• Now you can go back to the original application and instead of importing the data from the data source, you now have to import the QVD file

[Waïl_Riachi_Sanofi]

Hello @Andrei_Cusnir 

The format you see from my database is the format that Oracle (sqlDeveloper) decides to display (DD / MM / YYYY HH: MM: SS). However, just because Oracle decides to display the format like this does not mean that the data is saved and recognized in that format by QlikSense. Unless I am mistaken, QlikSense tries to recognize date fields thanks to QlikSense's natural script which is entered in the data load editor tab: TimestampFormat: 'M / D / YYYY h: mm: ss [.fff] TT' and DateFormat: 'M / D / YYYY' hence the fact that we see on my QlikSense table that my data is in the format 'M / D / YYYY h: mm: ss [.fff] TT' while on the screenshot from the database we see the format DD / MM / YYYY HH: MM: SS.

Now the question is: what is the format that QlikSense considers as date and puts the calendar logo icon next to the variable name once recognized? Like for example my variables r_modify_date and r_creation_date which have been recognized in date format and which have the calendar logo next to their name and yet they are structured exactly the same way as the other variables not recognized as date. Sometimes, the dates are recognized as date but there is not the calendar logo but an arrow allowing to have the expression functions (Month, Year, YearQuarter etc.) however, since there is no the calendar logo, there is no autoCalendar function and I need it to make my graphics

Come then the only solution is that the variables recognized as date are the variables which do not contain NULL data, I could see that in the database some date fields were 'non nullable', therefore when they were null the system (documentum) automatically filled in the fields with the date '1/1/0001'. The problem is that QlikSense doesn't seem to recognize dates in "1/1/0001" so you have to convert these dates to null date. I tried by adding calculated fields but when we create a calculated field we cannot modify its format (date, timestamp) and the calendar logo still does not appear ... so I cannot have it the autoCalendar on these calculated fields. The only solution therefore seems to be to write a script in the dataload editor which only retrieves non-null date data or which converts null (1/1/0001) to true null date (null date) which is recognized by QlikSense normally . While displaying my fields I sometimes have both "1/1/0001" in the "Table" chart and both "empty" data where there is nothing.

I think if the problem is not with "1/1/0001" it is rather caused by this empty data which is not recognized as null date (null date). If you have scripts to advise me, I would be happy to because I do not master the language of QlikSense and I have never written a script apart from the creation of expressions (measures). You will find attached a sample of data with which you can do your tests (once you have the calendar logo next to all the date fields, you will have succeeded) 😉

Thank you in advance,

Good luck !

Waïl Riachi

[Andrei_Cusnir]

Hello,

Thank you very much for all the information. You are absolutely right about the values that are stored in your Oracle database and why they are displayed the way they are. My responses contain mainly assumptions about what could possibly go wrong since I don't have the exact same setup on my side and the exact same dataset.

I took a look at the dataset that you have provided and I was able to reproduce the same issue that you have. Which means that we are on good point. During my investigation, while I was playing with the provided dataset, I have found some interesting observations. Here are the results of my investigation:

1. You are absolutely right that the issue is mainly caused on the fields that contain "Null" data.
2. The reason I put the "Null" in quotes, is because it is not actually Null but it contains the value "-1"
3. I have opened the file in Excel and I have noticed that where it supposed to have "Null" data it had "####"
4. Note: That you have to expand the columns full, because initially all the cells appear as "####" due to the fact that it is too small and can't fit the dates inside. So in that case expanding the width of the column, will show you the cells with the dates and the rest will still be "#####" 
5. Changing the format from "Custom" to "Text", I have noticed that for the values that the dates exists, you have an actual timestamp, but for the rest of the values, you have the number "-1"
6. I have then created a new copy of the sheet with the data and in the new copy, I have expanded all the column width to see which cells contains dates and which contain "#####". Then I have deleted the contents of all the cells that contain "#####".
7. Importing this data into the Qlik Sense, while using Data manager, I was able to see that all the fields that suppose to be having dates, had the Calendar icon, regardless the fact that some cells were missing data.

I assume that the data you have provided in the EXCEL is exported from the database, which means that instead of having null values, you are having -1. Qlik Sense is trying to interpret the values as dates and then it finds the number -1 which is confusing. This is why in your case you are getting a weird date as '1/1/0001', since it is failing to interpret it properly. Also I believe that in your use case scenario Data load editor is not going to help you, because you want to load the dates and let Qlik Sense apply the autoCalendar on them. This "Problem with creating calendar measure. No date fields detected" [1] article, explains that if you want the autoCalendar then you have to load the data with Data manager. 

Your dataset looks like this:

This is how the data looks after I have removed the "-1" from the dataset in EXCEL and reloaded:

I hope that this information was helpful. 

---

[1] https://community.qlik.com/t5/New-to-Qlik-Sense/Problem-with-creating-calendar-measure-No-date-field...

[Waïl_Riachi_Sanofi]

Hello @Andrei_Cusnir,

Hello, Thank you for your analysis. I am fully aware that Excel recognizes "Null" dates in "-1" it is precisely thanks to this test that I understood where the problem came from: the bad interpretation of the null dates in my database. data. However, my database is connected directly to QlikSense so it doesn't go through Excel and I don't know if QlikSense considers them as "-1" like on Excel. Rather, he seems to think of them as "1/1/0001". So the question is, how do I tell QlikSense to properly recognize dates in my database?

And for information, I have never used the Data Load Editor to load my data, I am all the time spent by the Data Manager.

Thanks in advance,

Regards,

Waïl Riachi

[Andrei_Cusnir]

Hello,

Thank you very much for sharing with me this information. I am not an EXCEL expert, therefore my investigation results are based on everything that I was able to reproduce or find in the official Microsoft Office documentation. Please find below the findings of my investigation:

1. As I have mentioned above, I am not an EXCEL expert, however I was unable to find any official Microsoft Office documentation which is stating that EXCEL interprets null values as “-1”. Do you have any official Microsoft Office documentation that can confirm your statement? Or perhaps any article that demonstrates how and why EXCEL might interpret a NULL value as an actual “-1” value?
2. I have extended even further my research and I have tired various software that handled null data and exported the data to EXCEL documents to see how the NULL values are interpreted in EXCEL. In all my attempts, I was getting a different outcome for my test data. Following the next 3 points, you will be able to see through my reproduction attempts, how EXCEL handled the NULL values.
3. My first reproduction was to create a demo local database, which I have filled in with data values and left some data as NULL. When querying my database, I was able to see that the NULL data was represented as “[NULL]”. Then I have exported the dataset into an EXCEL file and when opening the file with EXCEL, the null data was represented as “NULL” text.
4. For my second reproduction, I have created a table in Qlik Sense with dataset where some data was NULL. I have exported the data as an EXCEL file and when I have opened that file with EXCEL, I have noticed that the NULL values, were represented with “-” symbol.
5. For my third reproduction, I have created an Node.js application which is created an EXCEL document, fills the document with some demo data and adds some NULL values in the sheet. Then I have saved that document as EXCEL file and when opened with EXCEL, I have noticed that where the NULL values are supposed to be the cell was empty and had no data at all.

As you can see, through my reproduction attempts it seems that NULL values were interpreted differently in different environments. In my database the value was displayed as “[NULL]” but when exported it was displayed as “NULL” text. In Qlik Sense the NULL value was presented as “-” and when exported, it was presented as “-” as well. However, you can see that when writing a custom script that writes the exact NULL value in the EXCEL file, the value is not interpreted as “-1” by EXCEL as you have mentioned in your last response. Therefore, in your use case scenario, when you have exported the data from your database, then during the export process most probably the null values were exported as “-1” instead of NULL.

Qlik Sense doesn’t have a full control over the ODBC connector, therefore many things can play a role in why the data is loaded the way it is. To troubleshoot further the issue there are some options that you can try. I understand that you haven’t used Data load editor and you were only working with Data manager, however this will probably help with the investigation. If you are not very familiar with Data load editor and you can’t follow some of the recommendations mentioned below, I would suggest you to search online for simple tutorials on how to use it with basic Data load editor and also check other topics in Qlik Community. This will help you troubleshoot better the current issue and at the same time it will allow you to have more freedom and control over your dataset loading in the future.

Please share with us the following information:

1. To create the EXCEL file did you export from the database, or have you used the ODBC connector in EXCEL?
2. If you haven’t used the same ODBC connector in EXCEL yet, can you please use it and load some data? Please share a screenshot to show how the data appear in EXCEL in that case. This article [2] might help you with the setup.
3. You have mentioned that you were planning to upgrade to version August 2021, did you do so? Did it help to resolve the issue?
4. Try loading the data from the Data load editor and use the Text()[1] function. How is the data presented in Qlik Sense on your side? Can you share a screenshot?
5. Date formats are particularly hard to interpret properly as there are so many formats. Can you try loading your data in Qlik Sense by using Data load editor and apply the Text() [1] function to the fields that represent dates. After that you can create a new table where you are going to load the data from the first one, but this time, you will use the Date() [3] function and the Date#()[4] function to create the date with the format that you want. At the same time you can use the If()[5] function if you see that some of the values are “-1” or etc., where in that case you can return NULL() directly or otherwise the value itself.

---

[1] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretati...

[2] https://www.devart.com/odbc/sqlserver/docs/excel.htm

[3] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/FormattingFu...

[4] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretati...

[5] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalF...

[Waïl_Riachi_Sanofi]

Hello @Andrei_Cusnir ,

Thank you very much for your answer !

I am not loading my data with Excel but directly with a database connection to QlikSense. I wouldn't mind loading my data through the data editor if that's the only solution. But I would like to know how to do it and if possible send me the code to put so that I simply do a copy paste because I do not want to waste my time yet to train myself on this it already makes more than one While I am on this QlikSense issue I would like it to be resolved ASAP. And no we have not upgraded to the latest version of QlikSense because we will most likely give up our Qlik licenses and switch to another viewer if this problem cannot be resolved.

Thank you in advance for your help,

Regards,

Waïl Riachi