Multiple Joins to one field

[sebastiandperei]

Dear comunity,

In some cases, I needed to join a especific field to a table, in different conditions and instances. For example:

Let AP=Month#(Today());

Let PP=Month#(Today());

Sales:

Load

     Month,

     Sales

From Sales;

Left Join

Load

     Month,

     'Actual Period'  as Period_Name

Resident Sales

Where Month=$(AP);

When I want to add Period_Name for 'Past Period'

Left Join

Load

     Month,

     'Past Period'  as Period_Name

Resident Sales

Where Month=$(PP);

The table has already field Period_Name, and doesn't join it, just search Month field with $(PP) value and 'Past Period' in Period_Name, that obviously doesn't exists. In this simple example, i could resolve with If sentence in Period_Name assignation. But, I need to know how can I Join a field in different instances, for use it in other projects.

Thanks!!!

[rwunderlich · Accepted solution]

The solution is to build a temp table of the data you want to join and then do one Join.

Period_temp:

Load

     Month,

     'Actual Period'  as Period_Name

Resident Sales

Where Month=$(AP)

;

Load

     Month,

     'Past Period'  as Period_Name

Resident Sales

Where Month=$(PP)

;

Left Join (Sales)

LOAD *

RESIDENT Period_temp;

DROP TABLE Period_temp;

-Rob

http://robwunderlich.com

[sebastiandperei]

Thanks Rob!