Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Change the relation between two tables to outer join

Hi!


I have two tables (table1, table2). two fields are the same name (id), that is mean qlik sense will link them automatically. how can I get the date like (outer join). I mean if I have the visualizations (table and Barchart), then the data will show the matches rows. Will I be able have the data like right outer join?


Regards,

Ziad.

I have explained the question below in a comment in another way. Here it is.


Actually, I think it is better if I explain in full details. then maybe you can find a better solution. here is the explanation.

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.





1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Change the relation between two tables to outer join

You couldn't change the way how the tables are associated together. If it's not fit to your requirements you will need to adjust this within the script - maybe directly with join-statements but I assume you will need more efforts to transform your data then I think you are missing associations respectively the data to them: Generating Missing Data In QlikView.

- Marcus

4 Replies
MVP & Luminary
MVP & Luminary

Re: Change the relation between two tables to outer join

You couldn't change the way how the tables are associated together. If it's not fit to your requirements you will need to adjust this within the script - maybe directly with join-statements but I assume you will need more efforts to transform your data then I think you are missing associations respectively the data to them: Generating Missing Data In QlikView.

- Marcus

Not applicable

Re: Change the relation between two tables to outer join

Actually, I think it is better if I explain in full details. then maybe you can find a better solution. here is the explanation.

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.

MVP & Luminary
MVP & Luminary

Re: Change the relation between two tables to outer join

If I understand your doing right then you will need to create these missing data. This isn't a easy topic but the best start and a very good explanation could you find within the link from above.

Also you could consider not to show these data then:

The Importance of Nothing

NULL handling in QlikView

- Marcus

Not applicable

Re: Change the relation between two tables to outer join

Thanks a lot Marcus. you are right I need to generate the missed date as it is explained in the pdf in the first answer

Community Browser