1 Reply Latest reply: Feb 19, 2013 9:34 AM by Jonathan Brough RSS

Alternatives to outer joins

felipe carrera

hi

i wanted to know, if there are alternatives to performing an outer join in qlikview, regardless the fact whether the outer join is necessary or not.

Are link tables one alternative? are outer joins on the same table a good idea?

thanks for you help

felipe

  • Re: Alternatives to outer joins
    Jonathan Brough

    Outer join is essentially the default behaviour in QlikView, when two logical tables are associated together by both having the same field, and all the data is available for display. In this way whatever records are null will/can be ignored (if supressed on the front end) and a selection of fields from both tables (using list box filters on the front end) can act as a logical OR between fields on different tables.

    You can still use the Outer join is you want to collate all the fields into a table within QlikView, but such tables are just a way of thinking about, and controlling, the associations between the fields, due to the data not actually being stored within these tables but in their individual fields.

    As for a table structures, I typically pass through 3 stages of development:

    1. linked tables, which is useful with the way qlikview assoiates fields based on their names, but doesn't last very long as common dimensions lead to syn tables and loops

    2. a transaction table, with different transaction types concatenated into the same table in order to share common dimensions

    3. a link table, once i find that the transaction data is set at different levels, e.g. invoice rows being a lower level than invoice headers and accounts receivable.

    I'll always expect to get to a link table, if only for clarity and scalability. it does mean however that you have to be quite rigorous to make sure all the possible links are being filled in.

    Hope these thoughts make sense and help.

    Jonathan