Fields from single table not syncing correctly

[Benh09]

Hey all,

I have a table that is stored in a qvd file. The setup is as follows:

[UpdatedClients]:

LOAD

[ClientID],

[ClientName],

[CorporateID],

[CorporateName],

[BusinessClassID],

[BusinessClass]

 FROM [lib://Operations:DataFiles/UpdatedClients.qvd](qvd);

Upon using this table, with several others associated by ClientID, i created filter panes for ClientID and ClientName as some people use the ID while others use the name. However, when ClientName is selected all data goes blank, and the corresponding ClientID filter pane doesn't reference any ClientID correlated to the client name. Using the ClientID filter pane, everything works as expected with the exception of correlating to the client name filter pane. This is very confusing as they are fields on the same table, and all ClientIDs have a corresponding ClientName. Anyone have any thoughts on why this may be happening? Thanks.

[Or]

If ClientID is your key to other tables, it's possible and even likely that you're selecting ClientID values from those tables, not this one. However, without seeing the actual data, there's no way to know for sure. 

[Benh09]

I went back and ensured the ClientID was coming from the UpdatedClients table in the filter and i still get the same result. What might you need to see in order to determine the issue?

[Or]

Ah, your phrasing explains what I assume is the source of confusion. You can't control the "source" of a filter on a field. If your filter is for the field ClientID, it's going to list all values for ClientID regardless of the source table. 

If it is critical for you to only have values from a specific table, you can filter on a calculated dimension that only allows values from a specific table (e.g. ones that have a Client Name that isn't null). However, you won't be able to do this directly on a field. 

[Benh09]

Thank you for the explanation. I am loading the qvd files in the following manner:

[Calendar]:

LOAD

[CalendarDate],

[Year],

[Month],

[Day],

[Quarter],

[Week],

[DayOfWeek],

[MonthName],

[IsHoliday],

[IsWeekday]

 FROM [lib://Operations:DataFiles/Calendar.qvd]

(qvd);

[DailyPaymentTotals]:

LOAD

[CalendarDate],

[ClientID],

[TierNumber],

[PaymentAmount],

[CommissionAmount],

[PaymentCount]

 FROM [lib://Operations:DataFiles/DailyPaymentTotals.qvd](qvd);

[UpdatedClients]:

LOAD

[ClientID],

[ClientName],

[CorporateID],

[CorporateName],

[BusinessClassID],

[BusinessClass]

 FROM [lib://Operations:DataFiles/UpdatedClients.qvd](qvd);

[DailyPlacementTotals]:

LOAD

[CalendarDate],

[ClientID],

[TierNumber] AS [DailyPlacementTotals.TierNumber],

[PlacementAmount],

[PlacementCount],

[ConCount]

 FROM [lib://Operations:DataFiles/DailyPlacementTotals.qvd](qvd);

The DailyPayment and DailyPlacement tables are associated with the UpdatedClients table by ClientID. I guess my confusion is why when selecting the ClientID in one filter pane i get all the correct information. However, selecting ClientName from a separate filter pane returns no results. It is as if ClientName and ClientID don't see each other even though they are on the same table. Should i use joins instead of simply associations between tables in this case? Again thanks for the help.

[Chanty4u]

Associations are faster and cleaner in Qlik’s engine.

Could you please post your data model screenshot 

Do this step and try before

Text(Trim(ClientID)) as ClientID 

[Or · Accepted solution]

The easiest way for you to test this would be to "Select all" on [ClientName], and see if:

1) Any ClientIDs are available (white). Otherwise, there's something wrong with the table itself. 

2) Any ClientIDs are grayed out (excluded). If that's the case, you know that you're getting some ClientIDs from the other tables that are not listed in this table. 

3) Any values are possible (white) in the other tables. If selecting values in a single table excludes the other tables, your issue is probably with the joining field. In that case, either the data is not identical, the field type is not identical (one is text, one is numeric, for example), or something along those lines is preventing the rows from joining. You'd have to investigate that based on the actual data. 

[lennart_mo]

Another thing to look at might be the data for those two fields in the "Data model viewer".

For the ClientID I'd look at "Density", "Subset ratio" and "Present distinct values", for ClientName the "Density" should be the most interesting value.

Density shows the percentage of Non-Null-values per total rowcount.

Subset ratio tells you what percentage of values for a key field are present in that table. Calculated by dividing "Present distinct values" by "Total distinct values".

Based on your explanations so far I'd expect the "Density" and "Subset ratio" for both to be 100%

[marcus_sommer]

If the association of data doesn't work like expected or any other weird things happens it's the best to use a table-box with the relevant fields and not any charts or filter-panes.

In your case I would simply double the ClientID fields within the loads by adding the source information to the field - like: ClientID as ClientID_Payment and then using these 3 fields + the origin key and the name in the tablebox. Now you could see all relations between these fields and usually it becomes very obvious what is the cause of the unexpected behaviour.

Quite classical would be to see issues by linking numbers and strings, date and timestamps, leading/attached zeros and spaces or any other stuff of data-quality.  

[Benh09]

As i have this exact same setup in other applications with the ClientID and ClientName in separate filter boxes that work as intended, i know there is no need for me to trim any leading or trailing spaces on the ClientID field. However, as it isn't working now i will try anything! I attached the data manager and data model images. The synthetic key bothers me, but in other applications it does not cause this issue. Perhaps you can see something i cant. Thanks for the help!