Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
mazacini
Contributor III

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

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

1 Solution

Accepted Solutions
calvindk
Contributor III

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

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


View solution in original post

10 Replies
calvindk
Contributor III

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

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?

mwoolf
Honored Contributor II

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

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

mazacini
Contributor III

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

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
Contributor III

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

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
Contributor III

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

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


View solution in original post

mazacini
Contributor III

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

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
Contributor III

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

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

mazacini
Contributor III

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

Hi Anders

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

Highlighted
calvindk
Contributor III

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

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