Joining 2 tables

[sayadutt]

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:

[dplr-rn]

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

[sayadutt]

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.

[dplr-rn]

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.

[Rodj]

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