Left Join following Noconcatenate - behaves differently when more than one table loaded

[mazacini]

Hi

Code is as follows:

Contracts:LOAD CostCentre,
     Contract#FROM

(biff, embedded labels, table is A$);

Hours2:NoConcatenate load

     RecNo() as Rec,
     CostCentre,
     Field1,
     Field2,
     Field3
         FROM

(biff, embedded labels, table is C$);
left join (Hours2)
load   CostCentre,
     Contract#
Resident Contracts;Drop Table Contracts;

The code works fine when there is a single table X*.xls ie the Contract# field is joined, and a single Hours2 table is created.

However , when there is more than one X*.xls, the first table load behaves as above, but each subsequent table generates a new table - Hours2-1, Hours2-2, Hours2-3, Hours2-4 etc WITHOUT the Contract# field being joined.

If I drop the NOCONCATENATE, the load behaves normally. But I do need the NOCONCATENATE.

Any ideas?

Joe

[calvindk]

What are you trying to achive?

It sounds like you both want them all to be named Hours but you also want them to be separate tables?

I see now, you want to join them all. How much work would it be to just name them individually?

[m_woolf]

How about loading the Hours2 table first and then the other tables?

[mazacini]

Hi Anders

I am trying to add the Contract# field in the Contracts table to the Hours2 table. then drop the Contracts table.

I have a noconcatenate to avoid joining with a previous table load in the script which is not shown.

[mazacini]

Hi mwoolf

The script shown is an extract from a bigger script. The other tables need to be loaded first to make other script (not shown) work.

[calvindk · Accepted solution]

If you rename just 1 field name or add a dummy, it wont auto concatenate back, and you can load these hours tables without noconcatenate.

example:

Contracts:LOAD CostCentre,
     Contract#FROM

(biff, embedded labels, table is A$);

Hours2:load

     RecNo() as Rec,
     CostCentre,
     Field1,
     Field2,
     Field3,

     1 as dummyfield
         FROM

(biff, embedded labels, table is C$);
left join (Hours2)
load   CostCentre,
     Contract#
Resident Contracts;

Drop Table Contracts;

You will still have to deal with a synthetic key, but im guessing you already do.?

[mazacini]

Hi Anders

Thanks for the advice, which I hae worked into my script.

The thing I cannot understand is how the script works ok for the first table x*.xls, but not for second, third etc.

Any ideas?

Rgds

[calvindk]

It does that because the name in your left join is not correct for the following tables.

[mazacini]

Hi Anders

Sorry, I'm still a bit confused. Would you mind telling me which name is not correct?

[calvindk]

When you do the noconcat, qlikview makes each file load as Hours2, Hours2-1, Hours2-2, etc but your left join specifies Hours2 only.