Facing a typical issue with a data model design - I am attaching the qvw file with an example.
Having two tables
TABLE A - columns DT_1,DT_2, KEY_1 will be used to match identical record in TABLE B
DT_1,DT_2, KEY_1, KEY_2, AMOUNT_1 , TYPE
01/01/2014,01/30/2014, 1,3, 100, FROM_A
TABLE B - the columns DT_1 and KEY_1 will be used to relate a record in TABLE A, logic if (TABLE_B.DT_1 is between TABLE_A.DT_1 and DT_2 and TABLE_B.KEY_1 = TABLE_A.KEY_1)
DT_1,KEY_1, KEY_2, AMOUNT_2 , TYPE
01/02/2014, 1, 1, 25, FROM_B
So, I created a combined key based on num(DT_1) & '-' & KEY_1 as COMBINED_KEY, and used the above logic to map to the right records from TABLE B to TABLE A.. then generated a bridge table to move the common keys and finally merged TABLE A and TABLE B to single table. Till here straight forward. The reason to combine and create a COMBINED_KEY is to satisfy the following cases, but it was missing few scenarios which supposed to behave differently which i will explain.
I think it will be easy if I try to follow the steps and values as per the attached qvw file (please open the qvw file now) Also attached some images and other charts to the file, just to map to the input values with results in place, but the goal is on the top 3 text boxes with totals.
CASE 1: Clear all sections,Select from Type list box - select a value 'FROM A'
Behaviour : Should combine all totals from TABLE A based on COMBINED_KEY - which in this case (41640-1), so
Table A total - 100, and since the matching combined key (i.e. 41640-1) exists in TABLE B for 2 records - with total - 75
Expected: TABLE A= 100, TABLE B =75, TOTAL (A+B) = 175,So far good.
CASE 2: Clear all sections,Select from Type list box - select a value 'FROM B'
Behaviour : Should combine all totals from TABLE B based on COMBINED_KEY - which in this case -
the matching combined keyare 41640-1 & 41730-2 So the totals are sum of affected keys
Expected: TABLE A= 100, TABLE B =250, TOTAL (A+B) = 350. So far good
CASE 3: Clear all sections, Select from KEY_2 list box value '4' (please note, not from KEY 1)
Behaviour: Key2 value 4 is only in TABLE B with a row level value 100, but the total is coming as 175, the reason is the issue in the design. i.e. when KEY2 value 4 is selected, the COMBINED_KEY key for that row was (41730-2) and since the same key exists for the KEY2 value = 3 in TABLE B sum is combined for the both which is 100+ 75, But I am looking to get only 100 in this case. In other words I wanted to combine when the same COMBINED_KEY exits for TABLE A not within the same table (i.e. TABLE B in this case).
Expected: Not as expected. should result in TABLE A= 0, TABLE B =100, TOTAL (A+B) = 100.
CASE 4:Clear all sections, Select from KEY_2 list box value '3'
Expected : Key2 value = 3 exits in both TABLE A and TABLE B, so as per case 2 explanation,takes COMBINED_KEY = (41640-1 and 41730-2) so the result was all the totals for both keys. But that is not the expected behaviour, should result considering the KEY2 value 3 record in TABLE A and TABLE B (i..e 100 from table A and 75 from TABLE B).
Expected: Not as expected. should result in TABLE A= 100, TABLE B =75, TOTAL (A+B) = 175.
I appreciate the community for your contribution in helping others.
