Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
AndersGS
Contributor III
Contributor III

Problem loading Excel file with Year and Week to QlikView

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

Labels (2)
9 Replies
Or
Master II
Master II

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?

AndersGS
Contributor III
Contributor III
Author

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

Or
Master II
Master II

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.

AndersGS
Contributor III
Contributor III
Author

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

 

 

Or
Master II
Master II

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?

AndersGS
Contributor III
Contributor III
Author

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

Or
Master II
Master II

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

 

AndersGS
Contributor III
Contributor III
Author

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

Or
Master II
Master II

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.