13 Replies Latest reply: Aug 16, 2011 9:32 PM by Nick Gan RSS

    Joining 2 tables...

    Nick Gan

      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...
          Jonathan Dienst

          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

          • Joining 2 tables...
            Nick Gan

            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...
                Jonathan Dienst

                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...
                    Nick Gan

                    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...
                        Jonathan Dienst

                        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...
                    Nick Gan

                    Hi Jonathan,

                     

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

                    Thanks for your help.

                      • Re: Joining 2 tables...

                        Hi,

                         

                        See if the attached sample can help you.

                         

                        Cheers.

                          • Re: Joining 2 tables...
                            Nick Gan

                            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...

                                Sorry i didnt get you..

                                  • Re: Joining 2 tables...
                                    Nick Gan

                                    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...
                                        Quentin Richard Bitegue-Bi-Essono

                                        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

                                            ,

                                            ,

                                            ,

                                        ];

                                          • Re: Joining 2 tables...
                                            Nick Gan

                                            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....

                                • Joining 2 tables...

                                  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