Joining 2 tables...

[#1]

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.

[jonathandienst]

Hi

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

[#3]

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.

[jonathandienst]

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

[#5]

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

[jonathandienst]

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

[#7]

Hi Jonathan,

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

Thanks for your help.

[#8]

Hi,

See if the attached sample can help you.

Cheers.

[#9]

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.

[#10]

Sorry i didnt get you..