Design data mode to support all join type from UI selection

[#1]

I have tables as following and trying to design the data model that can support all different join types based on user section.

From UI a list box with values as following (from SELECTION_TYPE columns)

Case 1:  User selects TABLE_A *(All records in Table - A)

Output table:

TABLE_A.ID	Table_A_Value	TABLE_B.ID	Table_B_Value
101	abc	-	-
102	xyz	102	beta
103	pqr	-	-

Case 2:  User selects TABLE_A + TABLE_B *(All records in Table A and Table B)

Output table:

TABLE_A.ID	Table_A_Value	TABLE_B.ID	Table_B_Value
102	xyz	102	beta

Case 3:  User selects TABLE_B - TABLE_A *(All records in Table B but not in Table A)

Output table:

TABLE_A.ID	Table_A_Value	TABLE_B.ID	Table_B_Value
-	-	201	alpha
-	-	303	gama

Similarly the other cases.

Tables as following

SELECTION_TYPE:

LOAD * INLINE

[

SELECTION_ID,SELECTION_TYPE

1, TABLE_A *(All records in Table - A)

2, TABLE_B *(All records in Table - B)

3, TABLE_A - TABLE_B *(All records in Table A but not in Table B)

4, TABLE_A + TABLE_B *(All records in Table A and Table B)

5, TABLE_B - TABLE_A *(All records in Table B but not in Table A)

];

TABLE_A:

LOAD * INLINE

[

ID , Table_A_Value

101, abc

102, xyz

103, pqr

];

TABLE_B:

LOAD * INLINE

[

ID , Table_B_Value

201, alpha

102, beta

303, gama

];

[marcus_sommer]

It's quite unclear what do you want to do. Should it be (executed) on script-level or only to display data within the gui? And why (so complicated)?

- Marcus

[#3]

My apologies if it was not clear before. Let me try to explain as following..

The idea is to find the best possible design for all selection types as per the SECTION_TYPE values in the UI.

Regarding the complicatedness - I can't speak about others how they have approached these type of problems (since i am still new to qlik) , but the requirement is pretty common in the BI world, we have all type of join in relational world (not that i mean they are not in qlik) but qlik is association model, so any selections will affect entire data model.

User should have ability to  select certain value in the list box  (i mean the SELECTION_TYPE) to set the context for other filtering or calculations for his/her next level of selections.

Let's put with an example-

Example : Assume I have Amount column in TABLE_A (i could have given better column names/values in my previous post) .

CASE 1:TABLE_A *(All records in Table - A). Meaning the context made to LEFT JOIN to TABLE A (if you consider)

              SUM(AMOUNT) -  will include all rows from TABLE_A

CASE 2: TABLE_A + TABLE_B *(All records in Table A and Table B). Meaning the context made to INNER JOIN.

  SUM(AMOUNT) -  will include only rows from TABLE_A, that match TABLE_B. which ideally return value i.e.

    CASE2 of SUM(AMOUNT) <= CASE1 of SUM(AMOUNT) , because we will have equal or fewer records since the user is intended to set the context to INNER JOIN for all the expressions in the sheet.

Similarly the other values in the list box....

I hope i could now explain much better than i was before.

[marcus_sommer · Accepted solution]

That's not the way how qlikview worked. I have the impression that your thinking comes from a sql-world and within qlikview it's a lot easier to build a proper datamodel which contains all associations. I suggest you started here Get started with developing qlik datamodels and tries to build a very simple datamodel with star-sheme at the beginning and check how the data looked in tables and then add step by step more complexity.

- Marcus

[#5]

Thanks a lot Marcus for your comments. Though i was aware, In qlik it's different view in terms of data model due to it's association approach. I was initially thinking would it be possible to create a data model that would easy the UI or front end people, so that they can benefit from not knowing set analysis and functions related to it.  But I realize that it's not a good idea and makes the data model more complex and could lead to duplicate results. I agree with you 100%.