Resident Load Refuses to Create New Table

[#1]

History:

We were playing around with outer joins and the like when we found ourselves wanting to RESIDENT LOAD an outer joined table so that we could apply some additional logic based on some analysis of fields that came from the two original data sources we joined up using OUTER JOIN.  In the process, we stumbled on to some odd behavior of RESIDENT LOAD.  We took to some simplified examples and eventually even stepped away from RESIDENT tables, because we could reproduce the issue with just a small bit of code.

Problem/Hypothesis:

When you RESIDENT LOAD a table you must have at least one of the following differences between the source table (resident) and the newly created table:

• There must be some difference in the fields/columns between the source table and new table (order of fields does not matter).  This difference must be at least one of these:
  • There must be less fields/columns in the new table than in the source table
  • At least one of the fields of the new table must be aliased to have a different name than that of the source
  • A new field is added to the new table and given a name which does not match any name of the source table

Failure to do one of these will result in the RESIDENT table being loaded back on to itself creating duplicate data.

Sample:

I have attached the code which shows these anomalies.  When you reload the script you will notice that not all of the tables get created and the source table gets populated with lots of duplicate data.

Request:

We would very much appreciate it if someone could explain this strange behavior or validate it as a bug .

[jsakalis]

I think I know what you may be looking for.

Try putting NOCONCATENATE right before LOAD statements. Do one at a time to see what happens.

Note that synthetic keys will be created, which you should, in general, avoid. But, since you are just playing around....

[maxgro · Accepted solution]

when you load a table (with resident, from qvd, database, etc...) in Qlik and the loaded table has the same number and name of fields of an already loaded table, Qlik will concatenate (similar to sql union) the tables.

In your first example you get 1 table with 8 record. This is how Qlik works ("by design").

Region_Ref:

LOAD * INLINE [

RegionID_RegRef, RegionName_RegRef

1,East

2,West

3,North

4,South

];

// It is suggested to uncomment only one of these tests at time so that you can see the different behavoir of the RESIDENT LOAD

TEST1_Region_Ref:

LOAD

RegionID_RegRef,

RegionName_RegRef

RESIDENT Region_Ref

;

If you want 2 tables add a noconcatenate

TEST1_Region_Ref:

noconcatenate LOAD

RegionID_RegRef,

RegionName_RegRef

RESIDENT Region_Ref

;

Or I'm missing something?

[#4]

Thank you Massimo and John.  You are both correct and have solved the issue entirely.  I should add that in our original plans we never intended to keep the source table after the resident load, but now we know if we did want to keep it, we could with NOCONCATENATE.  Thank you!