topic join and aggregate in QlikView

join and aggregate

salezian — Mon, 06 Aug 2012 15:55:44 GMT

Hi Dear Qlikview Experts

I have 2 tables

TABLE A:

customer_id

item_id

amount

TABLE B:

item_id

dimension1

Question is: is it possible to join and aggregate data to dimension1 level in QlikView ? At the end I would like to have in QlikView results of SQL query:

Select customer_id,dimension1,sum(amount) from TABLE_A inner join TABLE_B on TABLE_A.item_id=TABLE_B.item_d. I know I can do it using SQL statement in script, but is it possible to do it QlikView engine ?

Re: join and aggregate

MayilVahanan — Mon, 06 Aug 2012 17:35:20 GMT

Are you like this?

Load * Inline

[

Customer_id,Item_id,amount

1,1,100

2,2,200

4,3,300

4,3,400

];

Load * Inline

[

Item_id,dimension1

1,XXX

3,YYY

4,ZZZ

];

NoConcatenate

LOAD Customer_id,Item_id,Sum(amount) as Amount Resident A Group by Customer_id,Item_id;

Inner join(C)

LOAD Item_id,dimension1 Resident B;

DROP Tables A,B;

Re: join and aggregate

salezian — Tue, 07 Aug 2012 06:46:53 GMT

Thanks for your answer. I'm afraid that result of your script won't be the same as result of following sql query:

customer_id,dimension1,sum(amount) from TABLE_A inner join TABLE_B on TABLE_A.item_id=TABLE_B.item_d

group by customer_id,dimension1

I need to group data in TABLE A by using dimension from TABLE B