Qlik Sense: loading tables with filter

Question

This is my load script:

All:
load *;
sql exec [StoredProcedure];

LastYear:
load *
resident All
where Ship_Year = year(now())-1;

ThisYear:
load *
resident All
where Ship_Year = year(now());

However, I'm not getting my LastYear and ThisYear tables loaded. What am I missing?

Answer 1

Likely what is happening here is automatic concatenation, which is where multiple tables are automatically concatenated because they have:

1. The same exact field names;
2. The same exact number of fields.

So basically if multiple tables are structured exactly the same, Qlik will automatically concatenate them together.

In your case that means that since your [LastYear] and [ThisYear] tables are loading the same exact fields as the [All] table (via load *), they are automatically being concatenated back into the [All] table.

To prevent the auto concat, you must either:

Use the NoConcatenate prefix

The NoConcatenate prefix prevents the table from being automatically concatenated even if it has the same exact fields as another table.

For example:

LastYear:
NoConcatenate load *
resident All
where Ship_Year = year(now())-1;

This method is simple but you have to watch out for synthetic keys, since you are forcing multiple tables to have all matching fields, which Qlik will of course associate on.

Add, remove, or rename fields in the new tables

Since auto concatenation happens when there are the same number of identically-named fields, you can prevent the behavior by simply changing the fields that the tables are loading.

For example:

LastYear:
load
    *
  , 1 as [Last Year]
resident All
where Ship_Year = year(now())-1;

ThisYear:
load
    *
  , 1 as [This Year]
resident All
where Ship_Year = year(now());

In that example, the [LastYear] table adds the new field [Last Year], and the [ThisYear] table adds the new field [This Year]. This ensures that all of our tables have at least one field that is not in the other tables, preventing the auto concatenation.

Just like the NoConcatenate method, this solution will cause synthetic keys to crop up unless they are handled later in the script.

Use the Qualify statement

The Qualify statement is a toggle statement that "turns on" field qualification for specific fields, where the fields in any tables that are loaded after the Qualify statement have the table name prepended to them. You can then "toggle off" that behavior using the Unqualify statement.

For example:

Qualify *;

LastYear:
load *
resident All
where Ship_Year = year(now())-1;

Unqualify *;

That will result in something like this:

So basically that script reads as: "Turn on field qualification for all fields (*) from now on. Now load the [LastYear] table where every field will automatically be renamed with LastYear. added to the start of each field name. Then turn off field qualification for all fields from now on."

You can also tell Qlik to qualify only specific fields, like so:

Qualify [colA], [Ship_Year];

Load * ...;

Unqualify [colA], [Ship_Year];