topic Re: Change the relation between two tables to outer join in Integration, Extension & APIs

Change the relation between two tables to outer join

Fri, 03 Jun 2016 09:57:03 GMT

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.

Re: Change the relation between two tables to outer join

marcus_sommer — Fri, 03 Jun 2016 14:12:09 GMT

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

Re: Change the relation between two tables to outer join

Fri, 03 Jun 2016 16:27:44 GMT

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.

Re: Change the relation between two tables to outer join

marcus_sommer — Fri, 03 Jun 2016 19:04:20 GMT

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

Re: Change the relation between two tables to outer join

Mon, 06 Jun 2016 10:54:29 GMT

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