Table not being loaded when using inner join and resident

[#1]

I am doing some test to load 2 tables that has the same keys. Using inner join, I was able to successfully load the Amount table together with the Budget table in one table, Amount. When I try create another table, CombinedTable, using a resident load on Amount, the CombinedTable is not being generated. Any idea ?

Amount:
load * Inline [
ID, TargetAmt
A, 100
A, 200
A, 300
B, 300
B, 400
C, 400
D, 500
];


Budget:
inner join Load * Inline [

ID, BudgetAmt
A, 700
B, 300
C, 600
E, 400
]
;

CombinedTable:
LOAD ID, TargetAmt, BudgetAmt
RESIDENT Amount;

DROP TABLE Amount;

[#2 · Accepted solution]

Rename all fields in CombinedTable ..

For instance ...

CombinedTable:
LOAD ID as id,
 TargetAmt AS TA,
 BudgetAmt AS BA
RESIDENT Amount;

[#3]

That did the trick. Thanks !

[johnw]

What's happening is that when you try to create a new table with exactly the same fields, QlikView by default just concatenates onto the old table instead. Then when you drop the old table, you're left with nothing. Renaming fields works because then the fields are different, so it doesn't do the default concatenation. But a simpler and more explicit way is to use NOCONCATENATE to tell QlikView not to concatenate the data.

CombinedTable:
NOCONCATENATE
LOAD ID, TargetAmt, BudgetAmt
RESIDENT Amount;

Carrying this a little further, I'm always explicit with QlikView about what I want it to do. When I join, I tell it which table to join to, even when that isn't necessary. When I concatenate, I explicitly tell it to concatenate, even when that isn't necessary. And if I think there's even a chance that QlikView might get confused and add my data to another table, I use noconcatenate. It's a little more trouble up front, but helps to prevent oopses.

[#5]

Good to know John. Thanks for the tip.