resident load, resident table joining primary table

[#1]

hi does anyone know why my FINALTABLE is joining my primary table? My FINALTABLE should be about 900000 rows like the primary table but its coming out at double thta.  My script is below:

------

[TempTable]:
Load
Consultant,
If(IsNull([Consultant Specialty Desc]),'UNKNOWN',
IF(MATCH([Consultant Specialty Desc],'U','Unknown Consultant'),'UNKNOWN',[Consultant Specialty Desc])) AS [Specialty Desc Lookup],
[Date (Path)],     
[District Code],
[District Desc],   
[Hospital Number],       
Lookup,     
[PATH TYPE],     
[Patient Type Desc],     
Test,
[Hospital Number Missing Flag], 
[Financial Year],
Month,
DayName,    
IF([Test Description]='No Description',Test,[Test Description]) as [Test Description],      
[Actual unit cost]
FROM
[\\Nmh-bus01\QVDataSource\Pathology\Updated Report 2011-12\Pathology1112.qvd](qvd);

     LEFT JOIN
  LOAD
  [Consultant Specialty Desc] as [Specialty Desc Lookup],
  FinalSpecialty
  FROM
  [\\Nmh-bus01\QVDataSource\Pathology\Updated Report 2011-12\Patholgy Specialty Lookup.xls]
  (biff, embedded labels, table is Sheet1$); 

  LEFT JOIN
  LOAD     
    [Specialty Code],  
       [Specialty Description],
       [BO Spec Desc] AS FinalSpecialty,   
       [Care Group Code],
       [Care Group Description],    
       [Local Spec Code],
       [Local Spec Desc],
       [Local Spec Group Desc]
             
  FROM [$(vLookupTables)\Specilaty Lookup.xls]
  (biff, embedded labels, table is [Specialty Lookup$]);

FINALTABLE:
LOAD
If(IsNull(Consultant),'Not Supplied', Consultant) as Consultant,
[Specialty Desc Lookup],
[Date (Path)],     
[District Code],
[District Desc],   
[Hospital Number],         
Lookup,     
[PATH TYPE],     
[Patient Type Desc],     
Test,
[Hospital Number Missing Flag], 
[Financial Year],
Month,
DayName,    
[Test Description],      
[Actual unit cost],
[Specialty Code],  
[Specialty Description],
If(IsNull([FinalSpecialty]),'NOT SUPPLIED', [FinalSpecialty]) as [Final Specialty],   
If(IsNull([Care Group Code]),'Not Supplied', [Care Group Code]) as [Care Group Code],
[Care Group Description],    
[Local Spec Code],
[Local Spec Desc],
[Local Spec Group Desc]
Resident TempTable;

DROP TABLES TempTable;

--------------

any help appreciated???  im finding the resident load confusing when used inconjunction with left join??

[johnw]

It appears that the list of fields is the same on the load from the temp table?  If so, QlikView would concatenate onto the temp table instead, doubling your rows.  However, it wouldn't then create your new table, and when you drop the temp table, it would then drop ALL data, so maybe that's not what's going on.  Still, I'd add a NOCONCATENATE before the load of your final table, just to be sure.

[#3]

Hi John,

I tried added the NOCONCATENATE prefix but still the problem exists.

I did some checking and it seems when i remove the  to left join tables it sort of works.  However i need these left join tables included.

Any ideas why the 'left join table are affecting this?

[#4]

What if you just add one "dummy field" at the end (before Resident TempTable)?

I would try it this way:

FINALTABLE:

Noconcatenate load ... ,

1 As TempField

Resident TempTable;

At the end you can "drop field TempField;" if it works..

Regards,

Miha

[#5]

Hi, the dummy field doesnt work.

The problem seems to exist with the LEFT JOIN tables.  If i remove the second LEFT JOIN TABLE below the final table seems ok.

The thing is i need the second left join table (below)

LEFT JOIN
LOAD
[Specialty Code],
[Specialty Description],
[BO Spec Desc] AS FinalSpecialty,
[Care Group Code],
[Care Group Description],
[Local Spec Code],
[Local Spec Desc],
[Local Spec Group Desc]

[#6]

Is there a difference if you use on both Left Join's this syntax for Left Join:

Left Join (TempTable)

It occured to me once or twice that when you made Left join over Left Join that it didn't work as I would expect it

[#7]

Hi the Left Join (TempTable) on both table doesnt work either.

Did you say that 2 LEFT joins can cause problems or unexpected behaviour?

[#8]

I said that it could cause unexpected behaviour - maybe only on some versions of QlikView...

Which QV version are you using?

[#9]

VERSION 9

[#10]

HI Jason,

     can you check one thing that if you load only this part of code than what is the no. of rows you get ? I mean that without using resident load.

So, that way we will get to know if this is becuase of concatenation or left Join..!!

------

[TempTable]:
Load
Consultant,
If(IsNull([Consultant Specialty Desc]),'UNKNOWN',
IF(MATCH([Consultant Specialty Desc],'U','Unknown Consultant'),'UNKNOWN',[Consultant Specialty Desc])) AS [Specialty Desc Lookup],
[Date (Path)],     
[District Code],
[District Desc],   
[Hospital Number],       
Lookup,     
[PATH TYPE],     
[Patient Type Desc],     
Test,
[Hospital Number Missing Flag], 
[Financial Year],
Month,
DayName,    
IF([Test Description]='No Description',Test,[Test Description]) as [Test Description],      
[Actual unit cost]
FROM
[\\Nmh-bus01\QVDataSource\Pathology\Updated Report 2011-12\Pathology1112.qvd](qvd);

     LEFT JOIN
  LOAD
  [Consultant Specialty Desc] as [Specialty Desc Lookup],
  FinalSpecialty
  FROM
  [\\Nmh-bus01\QVDataSource\Pathology\Updated Report 2011-12\Patholgy Specialty Lookup.xls]
  (biff, embedded labels, table is Sheet1$);

  LEFT JOIN
  LOAD     
    [Specialty Code],  
       [Specialty Description],
       [BO Spec Desc] AS FinalSpecialty,   
       [Care Group Code],
       [Care Group Description],    
       [Local Spec Code],
       [Local Spec Desc],
       [Local Spec Group Desc]
             
  FROM [$(vLookupTables)\Specilaty Lookup.xls]
  (biff, embedded labels, table is [Specialty Lookup$]);