Change the relation between two tables to outer join

Question

I have a table (table1) has fact data. Let's say (products, start, end, value1, month[calculated column]) are the columns and start and end columns are timestamp.

What I am trying to have is a table and bar chart which give me sum of value1 for each month divided by a factor number according to each month (this report is a yearly bases. I mean, I load the data into qlik sense for one year).

I used the start and end to generate autoCalendar as a timestamp field in qlik sense data manager. Then, I get the month from start and store it in the calculated column "month" in the table1 using the feature of autoCalendar (Month(start.autoCalendar.Month)).

After that, I created another table having two columns (month, value2) the value2 column is a factor value which I need it to divide the value1 according to each month. that's mean (sum(value1) /1520 [for January], sum(value2) / 650 [for February]) and so on. Here the month and month columns are relational columns in qlik sense. then I could in my expression calculated the sum(value1) and get the targeted value2 which compatible with the month for the table2.

I could make the calculation correctly. but still one thing is missed. The data of the products does not have value (value1 ) in every month. For example, let's say that I have a products (p1,p2...). I have data in the table 1 for (Jun, Feb, Nov), and for p2 for (Mrz, Apr,Mai, Dec). Hence, When the data are presented in a qlik sense table as well as in a bar chart I can see only the months which have values in the fact table. The qlik sense table contains (2 dimensions which are [products] and [month] and the measure is m1[sum(value1)/value2]).

What I want to have a yearly report showing the 12 months. and in my example I can see for p1 (only 3 months) and for p2 (4 months). When there is no data the measure column [m1] 0 and I want to have the 0 in my table and chart.

I am think, it might be a solution if I can show the data of the the qlik sense table as right outer join of my relation relationship (table1.month>>table2.month).So, is it possible in qlik sense to have outer join in such an example? or there is a better solution to my problem.

Answer 1

I know this questions has been answered and I quite like Stefan's approach but hope my answer will help other users. I recently ran into something similar and I used a slightly different logic with the following script:

// Main table
Sales:

Load * Inline [
    ProductId, Month, SalesAmount
    P1       , 1    , 10
    P1       , 2    , 20
    P1       , 3    , 30
    P2       , 1    , 40
    P2       , 2    , 50
];

Cartesian:
//Create a combination of all ProductId and Month and then load the existing data into this table
NoConcatenate Load distinct ProductId Resident Sales;

Join

Load Distinct Month Resident Sales;

Join Load ProductId, Month, SalesAmount Resident Sales; //Existing data loaded

Drop Table Sales;

This results in the following output table:

The Null value in the new (bottom-most) row can stay like that but if you prefer replacing it then use Map..Using process

Answer 2

Update

Got it. Not sure if that this is the best approach but in this cases I usually fill the missing records during the script load.

// Main table
Sales:
Load 
    *,
    ProductId & '-' & Month as Key_Product_Month
;   
Load * Inline [
    ProductId, Month, SalesAmount
    P1       , 1    , 10
    P1       , 2    , 20
    P1       , 3    , 30
    P2       , 1    , 40
    P2       , 2    , 50
];

// Get distinct products and assign 0 as SalesAmount
Products_Temp:
Load 
  distinct ProductId,
  0 as SalesAmount
Resident 
  Sales
;

join (Products_Temp) // Cross join in this case

Load 
  distinct Month
Resident 
  Sales
;

// After the cross join Products_Temp table contains 
// all possible combinations between ProductId and Month 
// and for each combination SalesAmount = 0
Products_Temp_1:
Load 
    *,
    ProductId & '-' & Month as Key_Product_Month1 // Generate the unique id
Resident 
    Products_Temp
;

Drop Table Products_Temp; // we dont need this anymore

Concatenate (Sales)

// Concatenate to main table only the missing ProductId-Month
// combinations that are missing
Load
  *
Resident 
    Products_Temp_1
Where
    Not Exists(Key_Product_Month, Key_Product_Month1)
;

Drop Table Products_Temp_1; // not needed any more
Drop Fields Key_Product_Month1, Key_Product_Month; // not needed any more

Before the script:

After the script:

---

The table link in Qlik Sense (and Qlikview) is more like full outer join. if you want to show the id only from one table (and not all) you can create additional field in the table you want and then perform your calculations on top of this field instead on the linked one. For example:

Table1:
Load
  id,
  value1
From 
  MyQVD1.qvd (qvd)
;

Table2:
Load
  id,
  id as MyRightId
  value2
From 
  MyQVD2.qvd (qvd)
;

In the example above both tables will still be linked on id field but if you want to count only the id values in the right table (Table2) you just need to type count( MyRightId )