Data model with combined Key (issue)

Thu, 06 Aug 2015 01:19:26 GMT

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.

Re: Data model with combined Key (issue)

Thu, 06 Aug 2015 15:35:24 GMT

The issue was resolved. I need to split the KEY_2 column from the bridge table and keep in the TABLE_COMBINED.

Below is the part of the query that will have to be changes and it works.

// combine TABLE_A and TABLE_B to one table

TABLE_BRIDGE:

LOAD Distinct

COMBINED_KEY

,KEY_1

//,KEY_2

,TYPE

Resident TABLE_A;

Concatenate(TABLE_BRIDGE)

LOAD Distinct

COMBINED_KEY

,KEY_1

//,KEY_2

,TYPE

Resident TABLE_B;

DROP Fields KEY_1,TYPE From TABLE_A;

DROP Fields KEY_1,TYPE From TABLE_B;

Join(TABLE_A)

LOAD * Resident TABLE_B;

DROP Table TABLE_B;

RENAME Table TABLE_A to TABLE_COMBINED;

PS - not that i intended to answer myself, but it might help other peoples' time in answering the questions that needs real attention

topic Re: Data model with combined Key (issue) in QlikView

Data model with combined Key (issue)

Re: Data model with combined Key (issue)