topic Re: Left Join following Noconcatenate - behaves differently when more than one table loaded in QlikView

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

mazacini — Mon, 13 May 2013 10:31:04 GMT

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

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

calvindk — Mon, 13 May 2013 11:15:47 GMT

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?

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

m_woolf — Mon, 13 May 2013 11:18:19 GMT

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

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

mazacini — Mon, 13 May 2013 11:44:03 GMT

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.

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

mazacini — Mon, 13 May 2013 11:45:35 GMT

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.

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

calvindk — Mon, 13 May 2013 11:49:09 GMT

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.?

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

mazacini — Mon, 13 May 2013 14:39:02 GMT

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

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

calvindk — Mon, 13 May 2013 15:01:38 GMT

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

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

mazacini — Mon, 13 May 2013 15:24:17 GMT

Hi Anders

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

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

calvindk — Mon, 13 May 2013 15:31:50 GMT

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

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

mazacini — Mon, 13 May 2013 15:47:38 GMT

Thanks Anders.

I thought the Noconcat only prevented current load from joining previously loaded tables. In fact, it governs the load of tables within the Noconcat statement.

Many thanks

Joe