Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can try concatenating your two tables:
LOAD invno, city, t1_amount FROM Table1;
CONCATENATE LOAD invno, city, t2_amount FROM Table2;
I tried, i did not get same output.
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
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