topic Re: Problem loading Excel file with Year and Week to QlikView in QlikView

Problem loading Excel file with Year and Week to QlikView

AndersGS — Wed, 08 Dec 2021 12:30:47 GMT

Hi,

I need load an Excelfile to a QlikView application. I have tried with the following script :

LOAD
Year,
Week,
%FOKID,
NoPackages
FROM [$(vG.ImportPath)File_to_load.xlsx]
(ooxml, embedded labels, table is QVData);

The file consists of YTD data with Year, Week and %FOKID together with a count of packages (NoPackages).

Now, after loading the application, all the data in the application is "filtered" to show only those weeks and %FOKIDs as in the file - what do I need to change in my script?

Year, Week and %FOKID are all existing fields in the application. NoPackages is new information which will be updated weekly in the file.

We are running QlikView 12.60, May 2021 SR1

Thanks,
Anders

Re: Problem loading Excel file with Year and Week to QlikView

Or — Wed, 08 Dec 2021 12:36:00 GMT

I'm a tad confused - what did you expect to see in your application, other than the values you actually loaded from the file? How would Qlik know about any other potential values? Are there other tables/files you're loading from?

Re: Problem loading Excel file with Year and Week to QlikView

AndersGS — Wed, 08 Dec 2021 12:49:57 GMT

Hi,

Keeping the existing values in the application as is, just appending the ones in the file. There are several tables loaded into the application; transactions, products, customers etc. So with my script Year and Week are actually replacing what's in the application already? Sorry for my lack of knowledge here.

Maybe load Year, Week like this for example?

Year as NoPackagesYear,
Week as NoPackagesWeek,

Anders

Re: Problem loading Excel file with Year and Week to QlikView

Or — Wed, 08 Dec 2021 12:55:37 GMT

What you're describing shouldn't typically happen, but it may result from any number of things:

1) Other tables may be attached to these tables/fields via specific JOIN or KEEP statements

2) There may be WHERE EXISTS conditions in your other loads based on these fields

3) There may be permissions (Section Access) based on these fields

When you add a field which name-matches an existing field, Qlik will automatically create keys (joins) based on these fields, but that doesn't actually eliminate any values already there. If you don't want these joins created, you can avoid them by renaming the fields as you suggested. If you do that, you may end up with an "island table" that isn't joined to any other tables, though - that may or may not be what you want.

Re: Problem loading Excel file with Year and Week to QlikView

AndersGS — Wed, 08 Dec 2021 13:44:30 GMT

Thanks for clarifying. There's a binary load of another application into the one I'm working on now and that's including this Calendar script (attached), not sure if it explains why I'm facing this issue. The Excel data needs to be joined to the existing data, not beeing an "island table".

Anders

Re: Problem loading Excel file with Year and Week to QlikView

Or — Wed, 08 Dec 2021 14:03:35 GMT

This shouldn't impact what data is displayed... but without access to the entire script, there's no way to be sure what's going on under the hood.

What makes you think the data is missing? Is it actually not in the dataset when you look at that field's values?

Re: Problem loading Excel file with Year and Week to QlikView

AndersGS — Wed, 08 Dec 2021 15:18:17 GMT

Ok, thanks. No, when choosing a %FOKID that is not among the ones in the file, no data is available in my application.

Just realized that Date (YYYY-MM-DD) can be added to the Excel file values. I will try to load it with Date instead of Week and Year and see if that helps.

Anders

Re: Problem loading Excel file with Year and Week to QlikView

Or — Wed, 08 Dec 2021 15:38:18 GMT

Is [%FOKID] a field from your original binary load? If it isn't, choosing it will naturally only filter things from the file...

Re: Problem loading Excel file with Year and Week to QlikView

AndersGS — Fri, 10 Dec 2021 08:59:24 GMT

Yes, [%FOKID] is a field from the binary load.

Re: Problem loading Excel file with Year and Week to QlikView

Or — Sun, 12 Dec 2021 09:21:16 GMT

I'd suggest you check that any key fields, such as the date, have identical values / formats. It sounds like there may be an issue with the associations between the tables.