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
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?
I made a sample QVW, it's easier to understand i think...
Thanks for your help.
TEST.qvw 143.0 K
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.
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?
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
LOAD * INLINE [
Yes, they are the same, i use different names because i was still testing the script.
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....
If I am right and I understand the main question right I think it can be done by doing te following.
Left Join (TEST)
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.