topic Table not being loaded when using inner join and resident in QlikView

Table not being loaded when using inner join and resident

Thu, 19 Nov 2009 06:12:56 GMT

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;

Table not being loaded when using inner join and resident

Thu, 19 Nov 2009 06:24:10 GMT

Rename all fields in CombinedTable ..

For instance ...


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

Table not being loaded when using inner join and resident

Thu, 19 Nov 2009 06:27:27 GMT

That did the trick. Thanks !

Table not being loaded when using inner join and resident

johnw — Thu, 19 Nov 2009 07:01:27 GMT

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.

Table not being loaded when using inner join and resident

Thu, 19 Nov 2009 22:19:21 GMT

Good to know John. Thanks for the tip.