topic Re: Excel load fails in App Development

Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 12:03:59 GMT

Each year, a new Excel file is added to the data load editor as shown in the screenshot. The excel file load for 2023 year fails - there are no added new rows to the table PiniginePramaTemp.

Maybe someone smart can figure this out?

Re: Excel load fails

p_verkooijen — Thu, 14 Nov 2024 12:16:49 GMT

@augustaszvirblis you do not get an error, just no additional lines fetched??

First setup a empty request by selecting the excel file for 2023

See if this produces rows on itself.

I see you manually setup the fieldnames, after the first table load you better tell Qlik to concatenate the data. Use CONCATENATE(PinigineParaTemp) instead of PinigineParaTemp:

Re: Excel load fails

Or — Thu, 14 Nov 2024 12:20:17 GMT

2023 is the only one which doesn't seem to have a 7-line header defined. Is that intentional?

Past that, it's hard to guess without the file...

Re: Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 12:22:57 GMT

Yes, I get no error, just no additional lines.

"First setup a empty request by selecting the excel file for 2023". After I do that I get no rows, but the code works with no error. (as shown in the screenshots)

Re: Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 12:25:53 GMT

Some columns appear, some don't

Re: Excel load fails

p_verkooijen — Thu, 14 Nov 2024 12:28:53 GMT

@augustaszvirblis what do you see when just selecting the Excel through Select data

It should give you this kind of view, you should be able to see file content

Re: Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 12:31:50 GMT

Here you go

Re: Excel load fails

p_verkooijen — Thu, 14 Nov 2024 12:40:13 GMT

@augustaszvirblis this seems ok, there is no where condition on loading this file only field renames right?

Do you have lines loaded if you only load 2023 data

Seeing the Syn table link field you have to use CONCATENATE(tablename) for each load after the first/initial load to this tablename to successfully combine the 2023 data to the old data.

Could you share the reload log if it's not working, and a screenshot of the data model viewer

Re: Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 12:49:19 GMT

"there is no where condition on loading this file only field renames right?"

No, there is no Where statements:

Still getting the synthetic keys:

Sorry, what do you mean by relaod log?

Re: Excel load fails

p_verkooijen — Thu, 14 Nov 2024 13:16:36 GMT

Hi @augustaszvirblis If you get a Synthetic key there are multiple tables containing same fields and others you have to fix this first.

For example

Tmp:
load * inline [
DayGoal, Date,
123, 15-1-2023
113, 20-2-2023
22, 2-5-2023
340, 21-9-2023
];

Tmp:
load * inline [
DayGoal, Date, Type
152, 20-9-2024, A
111, 20-9-2024, B
122, 21-9-2024, A
90, 21-9-2024, B
];

Will result in 3 tables

The first will be Tmp containing only a $Syn1 link Field (based on DayGoal and Date)

The second will be Tmp-1 containing the $Syn1 link Field (based on DayGoal and Date) and the extra field Type

The third is the $Syn1Table, containing the $Syn1 link Field (based on DayGoal and Date) and the common fields DayGoal and Date

When you use CONCATENATE(Tmp)

Tmp:
load * inline [
DayGoal, Date,
123, 15-1-2023
113, 20-2-2023
22, 2-5-2023
340, 21-9-2023
];

CONCATENATE(Tmp)
load * inline [
DayGoal, Date, Type
152, 20-9-2024, A
111, 20-9-2024, B
122, 21-9-2024, A
90, 21-9-2024, B
];

There is only 1 table Tmp containing all fields, the Type field will be empty for the first records.

If you manually reload you see this as "the log"

When you schedule the log you can download the log in a text file.

Re: Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 15:07:19 GMT

Interestingly I get this:

The municipality name repeats 4 times

Re: Excel load fails

augustaszvirblis — Thu, 14 Nov 2024 15:28:45 GMT

Interestingly I get this:

Municipality repeats 4 times

Re: Excel load fails

eddyvargas — Thu, 14 Nov 2024 17:16:54 GMT

2021 File has the first column without alias, for this reason is better use concatenate([table name])

Re: Excel load fails

Frank_S — Thu, 14 Nov 2024 22:38:59 GMT

Try this test :

Go to 'Create'>'Dataset'
Select 'upload and analyze' to create the app

Note if there are any differences in the original data connection 'From' line syntax between the original and new app and adjust accordingly if needed.

See highlight below:

Note: The above is for Qlik Cloud.

Use similar steps if using Qlik Sense on premise.

Create a new app in Qlik Sense and connect to the .xls file within the data load editor for example and 'insert'. Do again for the .xlsx file.
Then check the 'From' syntax for each file and compare to your original app and 'From' syntax.
Finally, check to see if the output in your new test app produces the additional rows that you were expecting.