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
Transaction table
| Item | QTY | Date |
|---|
| Item 1 | 5 | 1/3/13 |
| Item 1 | 2 | 1/3/13 |
Item 2 | 7 | 1/1/13 |
| Item 3 | 3 | 1/1/13 |
| Item 3 | 4 | 1/2/13 |
| Item 3 | 1 | 1/4/13 |
Work table
| Item | Date |
|---|
| Item 1 | 1/3/13 |
| Item 2 | 1/2/13 |
| Item 3 | 1/2/13 |
| Item 3 | 1/3/13 |
Result table
| Item | Last Work Date | QTY at Date |
|---|
| Item 1 | 1/3/13 | 7 |
| Item 2 | 1/2/13 | 7 |
| Item 3 | 1/3/13 | 7 |
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.
