Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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
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
Hi Jonathan,
I made a sample QVW, it's easier to understand i think...
Thanks for your help.
Hi,
See if the attached sample can help you.
Cheers.
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.
Sorry i didnt get you..