topic Re: Joining 2 tables in QlikView

Joining 2 tables

sayadutt — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have 2 tables - one loading data from excel file and other inline table.

Excel data:

Load

Month,

A:Pass,

A:Fail,

B:Pass,

B:Fail

from <excel file>

Inline Data:

Load * inline
[
Area, Parent
A:Pass, A
A:Fail, A
B:Pass, B
B:Fail, B
];

How I can join this 2 tables so that I get the data like below:

Re: Joining 2 tables

dplr-rn — Tue, 07 Jan 2020 18:20:42 GMT

You need to cross table load the excel file so that you have a table like Month,Area,Value

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Re: Joining 2 tables

sayadutt — Tue, 07 Jan 2020 19:23:56 GMT

I guess my issue is I need to avoid the cartesian joint. The grouping is not happening properly.

This is what I am getting if I create a pivot table

This is what I want, Parents A and B should be grouped properly.

Re: Joining 2 tables

dplr-rn — Tue, 07 Jan 2020 20:35:46 GMT

Not clear what you mean.

as far as i can see your issue is the values inside the area column of the inline table is same as column names in the other excel.

in such a case the joins doesnt work. instead you should convert the table while loading your excel to make it into a single area column and values using cross table load.

Re: Joining 2 tables

Rodj — Tue, 07 Jan 2020 21:36:38 GMT

Hi @sayadutt , I agree with @dplr-rn , I've provided an example of how to do the crosstable in your other post on a related topic, so hopefully that sets you on the right track. In that post I created a "Category" field with the Crosstable, but if you called it "Area" instead it would automatically associate with your inline table on that field and you'll have your solution to both questions.

Cheers,

Rod