Count on joined tables and linking

[#1]

I think this is a common issue in QlikView, at least I didnt found out a easy solution yet. 🙂


Maybe you could provide me with some suggestions how to solve this.
If I am on the wrong path here, please let me know if there is an easier way or a build in function.

Following issues need to be resolved:
1) Count all buttonClicked per %SaleID
2) Link the data together

Let's say this is my object structure. (see attached picture )

Sale:
load * inline [
%SaleID, Value01
 sale01, xxx
 sale02, xxx
 sale03, xxx
] ;
Item:
load * inline [
%ItemID, %SaleID, value
item01,sale01, xxx
 item02,sale01, xxx
 item03,sale02, xxx
 item04,sale03, xxx
 item05,sale03, xxx
 item06,sale03, xxx
];
User:
load * inline [
%UserID, %ItemID, buttonClicked
 user01, item01, 1
 user02, item03, 2
 user03, item06, 3
 user04, item02, 1
 user05, item03, 2
 ];
tmp:
load %SaleID resident Sale;
left join
load %SaleID, %ItemID resident Item;
left join
load %UserID, %ItemID, buttonClicked Resident User;
tmp2:
load %SaleID,%UserID, sum(buttonClicked) as ClickedPerUserPerSale
Resident tmp
Group by %SaleID,%UserID;
ClickedPerUserPerSale:
load %SaleID &'_' & %UserID as %UserSaleKey,ClickedPerUserPerSale Resident tmp2;
drop Table tmp, tmp2;
exit Script;

After summing up the buttonClicked i get a new table with the information i need.

Now I need to figure out how to link the objects together?


Any advise is welcome.

Thx and regards

JJ.