topic Re: Joining 2 tables... in QlikView

Joining 2 tables...

Sun, 14 Aug 2011 03:05:31 GMT

Hi,

If i have 2 tables.

USER_MASTER_DATA:

- USERID

- USERNAME

- COUNTRY

SESSIONS_TABLE:

- USERID

- SESSIONTIME

If i do a normal LOAD, i can easily make a table to see which user's session time. BUT only to those who had sessios.

How can i know for those who never had? i tried outer join, but it didn't really work and the table is messy because we have everything.

thanks.

Re: Joining 2 tables...

jonathandienst — Sun, 14 Aug 2011 11:51:00 GMT

If I've understood your question correctly, you probably need to join the session times in two steps - first a normal join, and second, a self-join in the results table to replace nulls with zeroes. Something like this:

USER_SESSIONS:
LOAD 
    USERID,
    USERNAME,
    COUNTRY,
From ...
;
Left Join (USER_SESSIONS)
LOAD 
    USERID,
    SESSIONTIME AS tmpSessionTime
From ...
;
Join (USER_SESSIONS)
LOAD 
    USERID,
    If(IsNull(tmpSessionTime), 0, tmpSessionTime) AS SESSIONTIME
Resident USER_SESSIONS;

Hope that helps

Jonathan

Joining 2 tables...

Sun, 14 Aug 2011 12:21:12 GMT

Hi Jonathan,

close, but the problem is, in session table, userid might not exist because Sessions table is a log, whoever connect, will have a row recorded.

So, the problem is, how do we know which users didn't connect?

USER_MASTER_DATA:

- USERID

- USERNAME

- QVW_Name

SESSIONS_TABLE:

- USERID

- SESSIONTIME

Thanks.

Re: Joining 2 tables...

jonathandienst — Sun, 14 Aug 2011 20:21:37 GMT

NickSatch

That's what the second join is for. All the users who did not connect and are without a sessions record in the log will have a null session time in the result table. The second join replaces these nulls with a zero. So zero session time means the user did not connect.

Regards

Jonathan

Joining 2 tables...

Mon, 15 Aug 2011 06:43:09 GMT

Hi Jonathan,

Says, if i wanted to filter by month, it wont work.

When i select Jul, from session table, the user who doesn't exist in session table will not show up.

Unless, there's a way to insert, Jul > user A > 0 (accordin to the IF statement) ?

Thanks,

nICK

Re: Joining 2 tables...

jonathandienst — Mon, 15 Aug 2011 18:06:07 GMT

Nick

Depends what you mean by filter. If you are displaying in a table or chart, (say dimensioned by user, and filtered by a date selection) be sure to turn off suppressing zeroes, as this will hide the users that did not connect in the filtered periods (session time = 0).

Regards

Jonathan

Re: Joining 2 tables...

Tue, 16 Aug 2011 05:28:03 GMT

Hi Jonathan,

I made a sample QVW, it's easier to understand i think...

Thanks for your help.

Re: Joining 2 tables...

Tue, 16 Aug 2011 05:58:02 GMT

Hi,

See if the attached sample can help you.

Cheers.

Re: Joining 2 tables...

Tue, 16 Aug 2011 06:32:25 GMT

Thanks BlackRocks,

That method will work when you already have the data in the data in the table, then u can use that to suppress.

For my case, we need to first create the data, by userID by month....etc. i think. and i'm still working on it.

Re: Joining 2 tables...

Tue, 16 Aug 2011 06:41:29 GMT

Sorry i didnt get you..

Re: Joining 2 tables...

Tue, 16 Aug 2011 08:28:00 GMT

The data that i have, only allows me to see who has used which QVW for N times.

What is doesn't allow is , we can't see who has not used which QVW in which month. because the data is simply not available.

We can do 2 tables, to present the data, but ideally, i'd like to have it in a single table, by userID, by QVW, by Month.

Joining 2 tables...

Tue, 16 Aug 2011 09:43:22 GMT

Hi nicksatch. Please tel me, why do u have to differentiate between QVW and QVW_session here? They seem to be the same, or not?

SESSION:

LOAD * INLINE [

UserID, QVW_session, access_DATE, session

A1, SALES, JAN, 23

A2, FI, JAN, 230

A3, FI, FEB, 45

A4, SALES, FEB,12

A5, FI, FEB, 2

];

USER_MASTER:

LOAD * INLINE [

UserID, QVW

A1, SALES

A1, FI

A2, FI

A2, SALES

A4, SALES

A5, FI

];

Joining 2 tables...

Tue, 16 Aug 2011 14:38:44 GMT

Hi all,

If I am right and I understand the main question right I think it can be done by doing te following.

TEST: LOAD * From USER_MASTER_DATA; Left Join (TEST) LOAD * From SESSIONS_TABLE

WHERE NOT USER_MASTER_DATA.UserID IN (SELECT UserUD FROM SESSIONS_TABLE);

Or something like this. I have not tested this and I have only a few minutes but my point is that maybe you can use something like where not

I hope this will help you a little bit.

Kind Regards,

Martijn

Re: Joining 2 tables...

Wed, 17 Aug 2011 01:32:05 GMT

Hi Biteguebiessono,

Yes, they are the same, i use different names because i was still testing the script.

Hi Martijn,

i think what we need to do now is to create a MONTH columns for MASTER_DATA table.

I tried joins but it work work,

because in my chart, i wanted to be able to select Month and look at that month's data.

e.g: in this month, who are those users who has been given access to a QVW but did not user it.

]the idea now, i can throw all the months we have in the sessions table into Index, then i=1 or something then loop it for all the users in MASTER_DATA table, month by month, BUT, i'm not sure if this will create a performance issue, because there are a lot of users to loop.

It seems harder than i initially thoughts....