4 Replies Latest reply: Aug 16, 2013 4:10 PM by dbnhc8tcims RSS

    How to match dates between tables and sum if <=

      Hey all,
      I'm pretty new to QlikView so sorry if this is basic or worded oddly, I'm still getting used to the vernacular as well. My problem is I have 3 SQL tables. One that contains items, one that contains transactions done against each item, and one that contains work done to each item. What I'm trying to do is find the last time work was done on each item and then sum all transactions that were done on the item prior to this date. So for example,
      Item table
      Items
      Item 1
      Item 2
      Item 3
      Transaction table
      ItemQTYDate
      Item 151/3/13
      Item 121/3/13

      Item 2

      71/1/13
      Item 331/1/13
      Item 341/2/13
      Item 311/4/13

       

      Work table

      ItemDate
      Item 11/3/13
      Item 21/2/13
      Item 31/2/13
      Item 31/3/13

       

      Result table

      ItemLast Work DateQTY at Date
      Item 11/3/137
      Item 21/2/137
      Item 31/3/137

       

      I am able to do all of this partially, but only if a single item is selected. Otherwise is shows 0 for all in 'QTY at Date.' I'm doing this by,

       

      (SUM(IF(Transdate<=DATE(FIRSTSORTEDVALUE(workdate,-workdate))),transqty))

      What I think is happening is that it is trying to compare the whole array of workdates, for all assets, to the transaction dates and when I select a single asset this solves the problem and it starts working. If you have any ideas of how to handle something like this better or what my problem might be I would appreciate it. Thanks. If you need any more information just let me know.