topic Re: Data loss due to join in QlikView

Data loss due to join

Thu, 21 Jun 2012 13:29:42 GMT

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

Re: Data loss due to join

john_duffy — Thu, 21 Jun 2012 13:40:28 GMT

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.

Re: Data loss due to join

Thu, 21 Jun 2012 13:42:23 GMT

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

Re: Data loss due to join

Thu, 21 Jun 2012 14:38:45 GMT

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?

Re: Data loss due to join

john_duffy — Thu, 21 Jun 2012 14:58:38 GMT

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.

Re: Data loss due to join

somenathroy — Thu, 21 Jun 2012 15:29:01 GMT

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

Re: Data loss due to join

Thu, 21 Jun 2012 16:08:20 GMT

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!