topic Re: QlikView Complex Task in QlikView

Joining Table Issue

nihhalmca — Thu, 16 Aug 2012 10:31:00 GMT

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.

Re: QlikView Complex Task

swuehl — Thu, 16 Aug 2012 10:41:56 GMT

You can try concatenating your two tables:

LOAD invno, city, t1_amount FROM Table1;

CONCATENATE LOAD invno, city, t2_amount FROM Table2;

Re: QlikView Complex Task

nihhalmca — Sat, 18 Aug 2012 05:57:31 GMT

I tried, i did not get same output.

Re: QlikView Complex Task

SunilChauhan — Sat, 18 Aug 2012 07:42:18 GMT

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

Re: QlikView Complex Task

swuehl — Sat, 18 Aug 2012 14:06:55 GMT

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