Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Which joins to which and what type of join?

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

3 Replies

Re: Which joins to which and what type of join?

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;

Re: Which joins to which and what type of join?

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

MVP
MVP

Re: Which joins to which and what type of join?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser