Imagine this scenario: we have a file in our database containing a respectable number of records (several million). The records show details of open amounts per week, for example:
Week No
Item (unique)
Amount
Sum
201138
A
100
201138
B
200
201138
C
50
201138
D
150
201138
E
250
750
201139
A
100
201139
B
200
201139
D
150
201139
F
2.500
201139
G
25
201139
H
400
3.375
As you can see, there is information for weeks 201138 (total amount 750) and 201139 (total amount 3.375). Some records that exist in week 201138 no longer exist in week 201139 (Items C and E); items F, G and H did not yet exist in week 201138 and where added in week 201139; items A, B and D existed in week 201138 and still exist in week 201139.
In this case, I want to report the total amount in week 201139 and the changes, as follows:
Week No
Item
Open amount
Mutation
201139
A
100
0
201139
B
200
0
201139
C
0
-50
201139
D
150
0
201139
E
0
-250
201139
F
2.500
2.500
201139
G
25
25
201139
H
400
400
This means that in fact I would have to copy the records that existed in week 201138 but no longer exist in week 201139 to the information for week 201139, with an open amount of 0 and a mutation consisting of the open amount x -1.
I have succeeded in finding the ‘deleted records’ by sorting by Item asc, Week No desc, and using the ‘Peek’ function. But as a result, the sum of mutations is listed in week 201138, not in week 201139 and therefore not where I want it!
I have been struggling with this challenge for over one week now…can anybody help?
I used a data island for the Items to generate a complete list of all Items to draw against in a chart.
Then I used two expressions with a set expression to limit the week to the max possible / selected week resp. the previous week and a simple if to limit the Items.
Their might be other solutions, this is just for a start up. I also left the Weeknumber as is, you will probably work on that to cope with year changes. A simple date using weekstart in your datamodel would probably do better then.
Thanks a lot for your answer, I'm marking it now as definitely helpful. I will have to study it some more to succesfully integrate it in my QVW - will mark the answer as 'correct' as soon as I succeed!
In the end, I solved this issue by reloading the data twice, once with the original weeknumber and again using the weeknumber + 1 with this instruction:
Year(MakeWeekDate(left(EJWKT1, 4), right(EJWKT1, 2))+7) & text(num(Week(MakeWeekDate(left(EJWKT1, 4), right(EJWKT1, 2))+7), '00')) asWeekNr FYI: Field EJWKT1 contains the weeknumber-value, i.e. 201144 for weekno 44 in the year 2011.
All fields being the same, the information was joined in one table automatically.
Next step was to count the number of similar records, adding the resulting value to the records with a Group By-clause. I then had a set of unique records with a count of either 1 or 2. Finding a '2' meant that the record existed in the original week AND in the next week (week + 1); therefore, it was unchanged. Finding a '1' indicated either a new record, or – if the same record existed in the previous week - a deleted record. Knowing this, I constructed this clause:
With this 'Status'-value, I was able to present the mutations related to the desired weeknumber. It was an interesting and at times frustrating learning experience for me; I hope others can benefit from this information, this community being a great place to find both useful information and inspiration!
