Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Left Join on resident tables

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.

4 Replies
Henric_Cronström

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

Not applicable

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

jonathandienst

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Henric_Cronström

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