Association between two tables is unmatched causing loss of data when filtering

[Smithereens]

Hello,

I currently maintain a mapping excel sheet (lets call it Table A) that I upload to Qlik. The file contains Dimension values that are then associated to a different table (Table B) that I am uploading through a QVD. 

The dimensions in Table A have an almost hierarchical structure of "Object" --> "Program" --> "Project", where a single Program can be aligned to multiple Objects and there can be multiple Projects aligned under a single Program. 

I have a requirement to show and drilldown from the Object level to the Programs and then Projects that are found in Table A, which is straightforward. But, I also have to show all other Projects not found in Table A that are associated with the Program/Object being filtered on (think of a long filter pane with all these fields next to each other drilling  down). The issue is that Table A is a much smaller subset of Table B and the Object Name field is only found in Table A.

I am associating the two tables through a primary key, but since Table A is only a subset of data, as soon as an Object Name field is selected the Project Field only shows values contained in Table A and there is no way to see the other Projects that are in Table B. The rest of the projects can be found within the tables if you were to only use fields native to Table B, but I need to use the Object Name field coming from Table A as a filter. 

So far I have tried ApplyMap() without success since it is a one to many relationship, as well as a Link table that I cannot get to work. 

Any help would be much appreciated. Thanks in advance!

[Sammy_AK]

is it possible to share the some examples in a table format. i would like to understand what primary key are you applying to link the tables between A and B. 

[sasikanth]

HI , 

Create a composite key to link two tables

------------------------------------------------

Table A:

Program &'-'&Project as NewLinkKey

Table B :

Program &'-'&Project as NewLinkKey

----------------------------------------------------

Reload and check subset ratio of NewLinkKey in both tables , if matching values are available. 

Then a selection in the object filed value of table A should display corresponding Programs & projects in Table B.

[Smithereens]

Unfortunately all of the data is confidential so I am unable to share any examples. My primary key is a concatenation of three fields, a Fiscal Year field, a Program code field and a Project code field. The issue is that my mapping file Table A is just a small subset of Table B. Therefor it does not contain all the programs and projects from the larger dataset of table b. So whenever I select a field from table A as a filter all of the other data that is found in table B is gone since I do not have those programs/projects defined as keys in my mapping file. 

[Smithereens]

If this helps everyone understand. 

I have a key found in my mapping file as well as the larger dataset (Table B):

2019_0100013F_631234

Where 2019 is the fiscal year, 0100013F is the Program and 631234 is the Project. 

The Program 0100013F has many more Projects aligned to it other than the one that is in Table A (mapping file). 

I only have the Object name field within my mapping file, so when  I choose one of those values all of the other projects that are not within mapping file are filtered out. I need to be able to display a column/filter pane item with projects in my mapping file and then projects that are outside of my map file. 

[Smithereens]

So after having tried this I feel like it is almost there. When I plot the NewLinkKey in a table along with the Project Fields from Table A and the Project Fields from Table B I am able to see all of the Projects in Table B that are not included in my mapping file Table A. Although,  whenever I apply the Object filter coming from my mapping file all the projects that are not contained in the file are still filtered out.

[sasikanth]

HI, 

Only related values will be shown for a selection in Qlik,  

sample dataset (masked, if confidential)  would be more helpful to understand your requirement. 

[Sammy_AK]

if you want to display all the values in a column you can use =only({1}Program) or =only({1}Project), this will list out all the values.

ideally, i see an issue with join/link to be used between Table A and Table B. i think it should be just Program, not a composite key (i.e. Fiscal year, Program, Project) or another option is to concatenate Table A and Table B and by using aggregate logic you can get all the information 

all the above are suggestions based on the explanation provided.