topic Re: Missing Table, Successful Load in App Development

Missing Table, Successful Load

fosterma — Wed, 29 Nov 2017 12:27:54 GMT

UPDATE: After raising with Qlik Support this issue has been flagged as a bug and passed to R&D.

Hey all,

I am loading in the below table to Qlik Sense using Data Load Editor then using Data Manager to Synchronise Scripts.

The data successfully loads and is visible and valid in Data Manager preview pane, however none of the fields are visible in edit mode and the table is missing entirely from Data Model Viewer.

After some troubleshooting I found that If I comment out "Start Date" and "DOB" from the script and re-Synchronise, the remaining fields become visible and the table loads fine.

I have tried renaming the 2 fields and explicitly defining the date formats in the scripts but makes no difference.

If I load the script without synchronising; the data loads fine, including Start Date and DOB. If need be I can write my own calendar scripts but I would prefer to find the cause of the issue.

Any help is appreciated. Not sure if it's a bug with the Nov release?

Version: Qlik Sense Desktop Nov 17 [11.24.1] and [11.24.3]

Master:
LOAD
    "Employee ID",
    "Payroll Number",
    ActiveEmployee,
    Surname,
    Firstname,
    EmployeeName,
    Gender,
    Date("DOB")   AS DOB,
    Date("Start Date") AS [Start Date],
    Date("Leaving Date") AS [Leaving Date],
    "Employee Fill %",
    "Job Fill",
    "Years Service",
    "Age",
    "Age Range"
FROM [lib://Data/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Missing Table, Sucessful Load

shraddha_g — Wed, 29 Nov 2017 14:21:53 GMT

If possible create a sample app using sample data and share it..

It will be easy to analyse the issue

Re: Missing Table, Sucessful Load

fosterma — Thu, 30 Nov 2017 13:08:09 GMT

Update: 07/12/2017: Issue still exists in Nov 17 Patch 1. I have uploaded Sample data and .qvf to the original post.

Workaround:

Downgraded from NOV 17 to SEP Patch 1.

Synchronised and Reloaded the script (No changes). App data is now loading correctly.

Seems like a bug with the Data manager in the latest version.

The QVD Contains confidential data, but I can try to replicate with a sample data if anyone is still interested.

Re: Missing Table, Sucessful Load

walkasia — Thu, 21 Dec 2017 19:08:58 GMT

So glad to find this post! Experiencing exactly the same problem. You saved me a lot of debug time by narrowing the problem down to the date fields.

I have noticed that if my Birth Date field (defined as date in mysql) gets added to the auto-generated autoCalendar script to derive the different calendar options, that is when the table stops being available in the sheets editor. Oddly, one of my datetime fields has the same issue, but the other datetime fields are fine even when they get auto-derived into autoCalendar. I haven't been able to identify why the datetime fields are not behaving consistently.

I tried a lot of workarounds, including selecting the Birth Date field out as a string, then forcing it back to a date in QlikSense. However, as soon as I tried to apply autoCalendar to it again, the table would disappear from the choices in the sheets editor. It seems to be something quirky between autocalendar and certain types of date fields.

In our case, we don't usually care to know the actual birth date. What we really want to know is either the persons age. So I am doing the age caculation in the load script sql:

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birth_date)), '%Y') + 0 AS age

This workaround has enabled me to move on, but I am sure this issue will come back again ... so I will continue to monitor this thread and post if I make any more headway.

FYI: I am running qliksensedesktop:11.24.1.

Re: Missing Table, Successful Load

walkasia — Tue, 16 Jan 2018 15:15:16 GMT

The problem is not limited to date fields. I added some "Replace" logic to a column in the Data Manager and immediately the table disappeared from the list of tables and fields available for use in the Sheets editors. Removing the Replace logic returned the table for use. Going to try and downgrade to the September version now.

Re: Missing Table, Successful Load

fosterma — Mon, 22 Jan 2018 13:37:59 GMT

Issue still exists in November '17 Patch 2

Qlik Sense Desktop: 11.24.4

Thanks walkasia‌ for the additional info

In my case, synchronise script generates garbage temp tables. In a specific example I have declared:

[Employee_Master]

Date(EmployeeMasterBirthDate) AS DOB,

However Qlik seems to want to repeat this step and dump the resident table. I've had issues like this in the past if I don't apply 'NoConcatenate' to the root table before dumping.

I always interpreted that you couldn't dump a resident table because it inherits the data from memory, so I usually have to force a separate table.

I tried applying 'NoConcatenate' to 'Employee_Master' as a test, but unfortunately no solution.

[Auto-generated section]

Unqualify *;

[Employee_Master_temp_0467b9ed-6e2a-e4d4-d1f9-b8453440]:
LOAD
[Employee ID],
[Payroll Number],
[ActiveEmployee],
[Surname],
[Firstname],
[EmployeeName],
[Gender],
Date([DOB] ) AS [DOB],
Date([Start Date] ) AS [Start Date],
[Leaving Date],
[Employee Fill %],
[Job Fill],
[Years Service],
[Years Service Range],
[Age],
[Age Range],
[Department],
[Contract Type],
[Labour Category],
[Contractual Hours],
[Job Title],
[Actual Start Date],
Date([Retirement Date] ) AS [Retirement Date],
[Temp Contract],
[Assignment],
[Temp Contract End Date]
RESIDENT [Employee_Master];

DROP TABLE [Employee_Master];

[Employee_Salary_temp_a1a3811a-b347-573f-5752-244c869b]:
LOAD
[Employee ID],
Date([Last Salary Review Date] ) AS [Last Salary Review Date],
Date([Next Salary Review Date] ) AS [Next Salary Review Date]
RESIDENT [Employee_Salary];
DROP TABLE [Employee_Salary];

RENAME TABLE [Employee_Master_temp_0467b9ed-6e2a-e4d4-d1f9-b8453440] to [Employee_Master];
RENAME TABLE [Employee_Salary_temp_a1a3811a-b347-573f-5752-244c869b] to [Employee_Salary];

[autoCalendar]:

[...]

If I 'Unlock' the Auto-generated section and remove the garbage temp tables (leaving only the 'autoCalendar'); the table loads. The tables are unsync'd again at this point but at least the table loads with the calendar intact.

Hope this helps someone more technically minded find a permanent cure

Re: Missing Table, Successful Load

Anonymous — Thu, 02 Aug 2018 07:00:44 GMT

Mark, did you get any further update on this? I am also facing the same issue (Garbage Temp Tables created and on removal, tables get unsync'ed.)

Yours Truly,

S.Manikantan

Re: Missing Table, Successful Load

Anonymous — Thu, 27 Sep 2018 14:11:06 GMT

This still happens in the April 2018 release.

Has anyone tried it in the June or September 2018 releases?

Re: Missing Table, Successful Load

Michael_Tarallo — Fri, 28 Sep 2018 12:53:25 GMT

Hi Guys - a colleague brought this to my attention. We experienced the same / similar issue with missing fields and we were using the June 2018 release. I am not sure if anyone tried it in Sept 2018 yet. fosterma‌ do you have the Qlik support case number? - it will help me locate this issue and have more info for R&D.

Thanks

Mike T

Qlik

Re: Missing Table, Successful Load

Anonymous — Mon, 01 Oct 2018 07:17:09 GMT

Thanks for the reply.

Not sure if it's useful and I'm not fosterma‌ who originally made this topic but I was given a case number when I replied to his message: 01491865.

I can't find that case in the support portal though.

Re: Missing Table, Successful Load

Anonymous — Tue, 02 Oct 2018 06:41:05 GMT

I'm waiting on the update patch but this has reportedly been fixed in the September 2018 release.