topic resident load, resident table joining primary table in QlikView

resident load, resident table joining primary table

Tue, 12 Jul 2011 17:46:19 GMT

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

resident load, resident table joining primary table

johnw — Tue, 12 Jul 2011 19:08:14 GMT

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.

resident load, resident table joining primary table

Wed, 13 Jul 2011 09:11:32 GMT

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?

resident load, resident table joining primary table

Wed, 13 Jul 2011 09:16:37 GMT

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

resident load, resident table joining primary table

Wed, 13 Jul 2011 09:58:57 GMT

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]

resident load, resident table joining primary table

Wed, 13 Jul 2011 10:44:42 GMT

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

resident load, resident table joining primary table

Wed, 13 Jul 2011 12:54:59 GMT

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

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

resident load, resident table joining primary table

Wed, 13 Jul 2011 13:00:18 GMT

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

Which QV version are you using?

resident load, resident table joining primary table

Wed, 13 Jul 2011 13:26:16 GMT

VERSION 9

resident load, resident table joining primary table

Wed, 13 Jul 2011 15:18:32 GMT

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

------

resident load, resident table joining primary table

johnw — Wed, 13 Jul 2011 20:05:29 GMT

OK, it could definitely be the left join. You can easily get more rows than expected when left joining. It looks like you're joining on FinalSpecialty? If, for example, you had two different specialty codes with the same BO Spec Desc, that would split rows apart when you left join, one for each matching code. This is a very common problem. It still catches me off guard fairly frequently, so I make sure to check that I only have the number of rows I expect after the left joins.

resident load, resident table joining primary table

Wed, 13 Jul 2011 20:46:21 GMT

Hi John, arhhh thanks for clarifying that it's probably a left join issue.

How would you recommend I resolve this issue? Not to use the second left join?

Or do you have any examples of similar 2 left join problems?

Regards

Jas

Re: resident load, resident table joining primary table

johnw — Wed, 13 Jul 2011 22:27:24 GMT

You first need to confirm that it's the left join. If you have more rows immediately after it than immediately before, then you have an unexpected one to many relationship between the old table and the new table. You would need to figure out why. Is there a data problem? If so can you fix it? Is the one to many relationship legitimate? If so, should you pick the "right" row to return it to 1:1? Should you preserve the one to many relationship by keeping the tables separate?

Doing a second left join is not the problem in and of itself. You can do any number of left joins. QlikView doesn't remember how many you did. It only knows what the table looks like now.

Here's a quick example problem:

Sales:
LOAD * INLINE [
Customer, Sales
Ann, 600
Bob, 300
Bob, 200
];
LEFT JOIN (Sales)
LOAD * INLINE [
Customer, City
Ann, San Francisco
Bob, New York City
Bob, Dallas
];

You'll end up with this:

Customer, Sales, City
Ann, 600, San Francisco
Bob, 300, New York City
Bob, 200, New York City
Bob, 300, Dallas
Bob, 200, Dallas

Chances are that's not what you wanted, but who knows what you wanted. What are these relationships? What information are we trying to convey? If Bob's 300 sale was in New York City, and his 200 sale was in Dallas, these tables do not tell us that. We would need additional information to join the data properly. Anyway, it's just an example of a left join giving you more rows than you started with.

resident load, resident table joining primary table

Wed, 13 Jul 2011 22:43:19 GMT

Hi, thanks for the explanation. I will have to run some tests tomorrow in the office.

All I know is that the problem arises with the sexon left join. Maybe I will have to look at this 'specialty table' and maybe what rows I'm trying to join.

As always with these things its trial and error. As I'm getting more competent with Qlikview I'm starting to use joins/concatebate/resident load and occassionly stumble problems.