Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

nihhalmca
Valued Contributor 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
MVP
MVP

Re: QlikView Complex Task

You can try concatenating your two tables:

LOAD invno, city, t1_amount FROM Table1;

CONCATENATE LOAD invno, city, t2_amount FROM Table2;

nihhalmca
Valued Contributor II

Re: QlikView Complex Task

I tried, i did not get same output.

chauhans85
Esteemed Contributor

Re: QlikView Complex Task

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

MVP
MVP

Re: QlikView Complex Task

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

Community Browser