Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nihhalmca
Specialist II
Specialist II

Joining Table Issue

Hi Experts,

Table1

invno, city, t1_amount

1110, Blr, 1000

1120, tn, 2000

1130, mum, 3000

Table 2

invno, city, t2_amount

1110, tn, 4000

1120, mum, 5000

1130, blr, 6000

I want to display in my application like

city, t1_amount, t2_amount

blr, 1000, 6000

tn, 2000, 4000

mum, 3000, 5000

How can i do please share solution.

4 Replies
swuehl
MVP
MVP

You can try concatenating your two tables:

LOAD invno, city, t1_amount FROM Table1;

CONCATENATE LOAD invno, city, t2_amount FROM Table2;

nihhalmca
Specialist II
Specialist II
Author

I tried, i did not get same output.

SunilChauhan
Champion
Champion

use below code

table1:

Load * inline [

invno, city, t1_amount

1110, Blr, 1000

1120, tn, 2000

1130, mum, 3000

];

Table2:

Load * inline [

invno, city, t2_amount

1110, tn, 4000

1120, mum, 5000

1130, Blr, 6000

];

table3:

load city, t1_amount,

1 as Key

resident table1;

Concatenate

load  city, t2_amount,

1 as Key1

resident Table2;

drop tables  table1,Table2;

and take a pibot table

city in dimension

and in expression

sum(t1_amount)

sum(t2_amount)

name in table should be in same case

hope this help

Sunil Chauhan
swuehl
MVP
MVP

Nihhal,

what do you get then?

If I do a CONCATENATE LOAD like described above, then create a table chart with dimension city and two expressions

=sum(t1_amount)

and

=sum(t2_amount)

I do get the table you requested above (note that wrote one time Blr and one time blr as city name value, which QV will take as two distinct values. I assume this is just a typo, but you can use

lower(city) as city,

in your loads to get all city names in lower case.

If you want to get a table in your data model (not in the frontend) like you shown above, you can do the concatenate load, the do a group by city:

table1:

Load * inline [

invno, city, t1_amount

1110, Blr, 1000

1120, tn, 2000

1130, mum, 3000

];

Table2:

Concatenate Load * inline [

invno, city, t2_amount

1110, tn, 4000

1120, mum, 5000

1130, Blr, 6000

];

LOAD city,

sum(t1_amount) as t1_amount,

sum(t2_amount) as t2_amount

Resident table1 group by city;

drop table table1;

which is basically the same idea as with the table chart, only all be done in the data model.

It really depends what you want to do with your data, if you need to keep the original information or not.

Regards,

Stefan