Data loss due to join

[#1]

Hello everyone,

I just started exploring the possibilities of QlikView a week ago and struggle with the following problem. My table structure is shown in the Screenshot, essentially I have two tables users and messages. The users table contains 200 users and the messages table 1000 messages. These message are, however, created by 196 out of the 200 users. When I now want to calculate the number of users, e.g. via count(DISTINCT mc_u_id) I receive 196 users instead of the desired 200.

Does anyone know how to solve this problem?

Best regards,

Stephan

[john_duffy]

Hi Stephan.

How are you joining the two tables - join, left join, ...?

If you left join the message table to the user table, all users will remain and your count should be correct.

John.

[#3]

Hi,

thanks for your reply. I only import the tables into QV and they are associated due to the common attribute. The join is not explicitly specified in the script.

Stephan

[#4]

I justed checked the tables under document settings, the user table has 200 entries. Does anybody have an idea how I can show the 200 users independent from the messages?

[john_duffy]

Hi Stephan.

I've attached a sample application using inline load to simulate your data.

Please take a look at the application and let me know how the table relationship differs from that in your application.

Are your User IDs a primary key (ie. unique).  If not, the distinct clause will eliminate duplicates.

John.

[somenathroy]

Take a Chart(Straight Table). Add 'mc_u_id' in Used Dimentions list. Select the added dimention and check 'Supress Value is null' check box. Then write an expression like Only(mc_mf_text). In Presentation Tab uncheck 'Supress Missing' and 'Supress zero-values' checkboxes.

This will give you all users independent from having messages.

Regards,

Som

[#7]

Oh my god, sorry, that was completely my mistake. I have a report over several sheets and I selected data on one sheet and then saw only a limited amount of values in the other... Sorry about that.

Thanks a lot for your help anyway!