2 Replies Latest reply: Jul 25, 2013 11:03 AM by Phelisa Moss RSS

    How to create temp table or a full join?

      Hi

       

      I am new on Qlikview and I am trying to create a temp table that will house data from 3 tables (Current Year, Budget, Previous Year) containing the same columns  except for 1 column in each table i.e. CY Amount for Current Year, Budget Amount for Budget and PY Amount for Previous Year being different. Qlikview automatically associates the tables according to columns containing the same data or what it recognises as a key field, my problem comes in when I try to get the totals for each GL Account that exists in all three tables only the ones refered to in my Fact table are displayed for all 3 amounts.

       

      Normally what I would do in SQL is to create a temp table with the common columns, add the 3 amount columns then retrieve the data from the different tables and insert into my temp table.  This helps me avoid skipping any data just becaue the GLAccount in my dimension table is not found in the Fact table, instead if the GLAccount in Budget table does not exist in Current Year table a zero value must be displayed for Current Year field and the sum of the Budget Amount column displayed for the Budgets table.  Sort of like a Full outer Join effect where all the info is returned from both tables.

       

      Hope its understandable