Which joins to which and what type of join?

[#1]

Hello guys,

I'm a little bit of blurry on the JOIN part. Say I have two table as pictured below:

The table Calendar contains the FiscalYear, FiscalMonth and Days.

Table Revenue might not have all of the revenue of a particular Fiscal Year and FiscalMonth.

To which table must a table join and what type of join must I use to derive the table Revenue1?

Thank you for your kind attention guys. Appreciate any suggestions/opinions and advices on this one. Thanks guys

[its_anandrjs]

You can concatenate this two tables that is Revenue and Revenue1 otherwise use simple join

1.With Concatenate

Load Country,FiscalYear,FiscalMonth, Amount

From Location;

Concatenate

Load Country,FiscalYear,FiscalMonth, Days, Amount

From Location;

2. With Join

Load Country&FiscalYear&FiscalMonth as %key, Amount

From Location;

Join

Load Country&FiscalYear&FiscalMonth as %key, Days, Amount

From Location;

[ashfaq_haseeb]

Hi Khairul,

just to add to anand's answer for concatenate part. add flags even to differentiate table.

1.With Concatenate

Load Country,FiscalYear,FiscalMonth, Amount, 1 as Flag

From Location;

Concatenate

Load Country,FiscalYear,FiscalMonth, Days, Amount, 2 as Flag.

From Location;

Regards

ASHFAQ

[jonathandienst]

Hi

Your calendar should have a date field as well,

     Calendar: Date, FiscalYear, FiscalMonth,Day,......

Then include the revenue date in the revenue table and combine the two revenue tables by concatenating. You may need to add a source code if you need to select data from only one of the revenue tables.

Remove the date fields other than date from the revenue table. Your revenue table is linked on date and will get the derived date fields by association.

HTH

Jonathan