topic Left Join on resident tables in QlikView

Left Join on resident tables

Mon, 19 Mar 2012 16:57:21 GMT

Hi all,

I'm getting a little bit frustrated here. Seems fairly easy but I just can't figure it out. Please someone help!

I have the following script. This is just the last part where I'm stucked; I have already defined HYP2 table:

[HYP3]:

LOAD [n cli],

[n carrier],

[contracts],

[policy],

[n invoice]

if([n carrier] = '000253', if(left([policy], 3)='920', 'Global', if(left([policy], 3)='609', 'Global', 'General'))) as [short_policy]

resident [HYP2];

drop table [HYP2];

//about 30k rows

[temp]:

LOAD [n cli], [n carrier], 'Global2' as [Filter_Global] resident [HYP3] where [short_policy]='Global' and [n carrier] = '000253';

//result is 100 rows

Up to here, no problem. Now all I want to do is create one table that holds all the 30k rows plus the [Filter_Global] field where the conditions are fullfilled.

How do I do this hell of a left join????? And what tables should I then drop?

The result should provide a table made up of 30k rows but with more then 100 values in the [Filter_Global] field since each [n cli] may have more [contracts].

Thank you, if you have the solution. Thank anyway for giving it a shot.

Left Join on resident tables

hic — Mon, 19 Mar 2012 17:58:21 GMT

Wouldn't a preceding load do the trick?

Try

[HYP3]:

LOAD *,

if( [short_policy]='Global' and [n carrier] = '000253', 'Global2', null()) as [Filter_Global];

LOAD

[n cli],

[n carrier],

[contracts],

[policy],

[n invoice]

if([n carrier] = '000253', if(left([policy], 3)='920', 'Global', if(left([policy], 3)='609', 'Global',

'General'))) as [short_policy]

resident [HYP2];

drop table [HYP2];

/HIC

Left Join on resident tables

Tue, 20 Mar 2012 07:55:34 GMT

Hi Henric,

thanky for your reply. I tried implementing a preceding load, but what happens is that the first load limits the second load to only those values for the conditions in the firs load are fullfilled.

In other words, the two loads are not loading all the 30k rows adding the 'Global' tag to some rows, but loading only those rows that have the 'Global' tag.

Any other suggestion?

Say now, with these two loads I have the 100+ rows marked. How can I add all the rows the original rows excluding repetitions?

Thanks

Left Join on resident tables

jonathandienst — Tue, 20 Mar 2012 08:03:07 GMT

Just replace the [temp]: label with left join (HYP3) That should work.

(Would give you the full code, but for some reason I cannot paste into a forum post from this PC)

Regards

Jonathan

Left Join on resident tables

hic — Tue, 20 Mar 2012 13:03:38 GMT

You should avoid Left Joins in this case, because it will potentially change the number of records and you do not want this. (It will if you have multiple records with the same combination of [n cli] and [n carrier].)

My suggestion does not contain a where-clause in the preceding Load, so it will not change the number of records. I.e. if you have 30k records in the "LOAD ... resident [HYP2]", then you will have the same number after the preceding Load also.

And you want to define the [Filter_Global] from these 30k records, right? Then a preceding Load is the correct way to do it. Or am I missing something?

/HIC