0 Replies Latest reply: Apr 6, 2011 2:04 PM by Thomas Winczura RSS

    Count on joined tables and linking




      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 )


      load * inline [
      %SaleID, Value01
      sale01, xxx
      sale02, xxx
      sale03, xxx
      ] ;
      load * inline [
      %ItemID, %SaleID, value
      item01,sale01, xxx
      item02,sale01, xxx
      item03,sale02, xxx
      item04,sale03, xxx
      item05,sale03, xxx
      item06,sale03, xxx
      load * inline [
      %UserID, %ItemID, buttonClicked
      user01, item01, 1
      user02, item03, 2
      user03, item06, 3
      user04, item02, 1
      user05, item03, 2
      load %SaleID resident Sale;
      left join
      load %SaleID, %ItemID resident Item;
      left join
      load %UserID, %ItemID, buttonClicked Resident User;
      load %SaleID,%UserID, sum(buttonClicked) as ClickedPerUserPerSale
      Resident tmp
      Group by %SaleID,%UserID;
      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