Left Join with Where-clause on Field of first table

[#1]

Hi Community,

I have a question about the syntax for the "Left Join". Is it possible to access a table field of the first LOAD in a Where clause in the LOAD of the left join?

Table1:

LOAD

     %Key,

    Field1,

     Field2,

     Field2

resident table_temp1;

Left Join (Table1)

LOAD

     %Key,

     Field4,

     Field5,

     Field5

resident table_temp2

where Field1 >= Field4;

Regards

Henning

[swuehl]

No, you can't access the Table1 fields in your second load where clause like this.

But you can join your tables and then use a subsequent load to filter your records.

[#3]

Is it possible for you to give me an example?

[swuehl · Accepted solution]

Using your script:

Table1:

LOAD

     %Key,

    Field1,

     Field2,

     Field2

resident table_temp1;

Left Join (Table1)

LOAD

     %Key,

     Field4,

     Field5,

     Field5

resident table_temp2;

ResultTable:

NOCONCATENATE LOAD * resident Table1 where Field1 >= Field4;

drop table Table1;

There might be a way to do it similar to your original approach using lookup() function, but I think this will not be very performant.

[#5]

THX!

[#6]

I've been trying to do exactly what your example shows here, but I'm having problems. When I reloading the resident table "FiscalWeeks" I am giving it a new identifier called "Calendar". But when I run the reload it appears to re-load the resident data into "FiscalWeeks", then gives me an error on the Left Join statement that the table "Calendar" is not found.  Any help you can give would be appreciated.

Below is my code:

Calendar:
LOAD
     FiscalYear,
     WeekNo,
     WkBegin,
     WkEnd
resident FiscalWeeks;

Left Join (Calendar)
LOAD
     FiscalYear,
     DatePattern,
     PeriodNo,
     PeriodBegin,
     PeriodEnd
resident FiscalMonths;

FiscalCalendar:
NOCONCATENATE LOAD
  *
  resident Calendar
  where date(WkEnd, 'YYYY/MM/DD') >= date(PeriodBegin, 'YYYY/MM/DD') and date(WkEnd, 'YYYY/MM/DD') <= date(PeriodEnd, 'YYYY/MM/DD');

Thanks!

[swuehl]

If your FiscalWeeks table shows the exact same number of fields and field names like your Calendar table, QV will autoconcatenate the two tables, thus there will be no Calendar table that you can reference in your left join.

You can avoid the auto-concatenation by using an explicite NOCONCATENATE LOAD prefix.

Calendar:
NOCONCATENATE LOAD
     FiscalYear,
     WeekNo,
     WkBegin,
     WkEnd
resident FiscalWeeks;

Left Join (Calendar)
LOAD
     FiscalYear,
     DatePattern,
     PeriodNo,
     PeriodBegin,
     PeriodEnd
resident FiscalMonths;

FiscalCalendar:
NOCONCATENATE LOAD
  *
  resident Calendar
  where date(WkEnd, 'YYYY/MM/DD') >= date(PeriodBegin, 'YYYY/MM/DD') and date(WkEnd, 'YYYY/MM/DD') <= date(PeriodEnd, 'YYYY/MM/DD');

You probably need to drop some of your temporary tables to prevent QV from building large synthetic keys.

DROP TABLE Calendar;

[#8]

Ah yes, I knew it was going to be simple. That did it! Thanks for your help!

Keith Hughes

http://mce_host/message/342908/cid:image001.jpg@01CE4AF9.931C2350

[#9]

Now I'm having problems with last step, where I use a where clause to pare down the value in the Calendar table to be just what I want in the final result. I swear i saw this work once, but it hasn't since and I'm not sure why. My code as it stand now is:

Calendar:
NoConcatenate LOAD
     FiscalYear,
     WeekNo,
     WkBegin,
     WkEnd
resident FiscalWeeks;

Left Join (Calendar)
LOAD
     FiscalYear,
     DatePattern,
     PeriodNo,
     PeriodBegin,
     PeriodEnd
resident FiscalMonths;

DROP Tables FiscalWeeks, FiscalMonths;

FiscalCalendar:
NoConcatenate LOAD *
  resident Calendar
  where ((WkEnd >= PeriodBegin) and (WkEnd <= PeriodEnd));

Everything is working except for the where clause in the creation of FiscalCalendar. No matter what I do I get all of the records that exist in Calendar, even though there are records that don't meet the criteria.

[#10]

After lookin into this more I began to see that the Where clause did appear to be working, however i couldn't see that in my results. The table box I had to show me the results of the FiscalCalendar table would always show me all entries that would exist in the Calendar file. If I store FiscalCalendar as a QVD file and then pull it into another project than I would see only the entries that match the where clause.

Any ideas on this one?