10 Replies Latest reply: Sep 7, 2015 12:17 AM by Kumar Pramod RSS

    adding two columns

    Kumar Pramod

      Hi all,

       

      I have two tables signupactivity and useractivity.

       

      I have used signupactivity table in other sheets. Now i need to add a new table useractivity for new sheet.

      One of the field from signupactivity i.e referrer_email_type_id which has the information about email_type_id.

      In the same way "status" field from useractivity table has the information about email_type_id.

       

      For example: if referrer_email_type_id  has info about email_type_id(44,45,46,47) and

      status has info about email_type_id(2,3,4,8).

      Now i need to add the both the columns into single column.

      I used join, concatenate it din't worked.

       

      The issue is signupactivity  is using invitation_id has association key to link with other table.

      This invitation_id  is not in useractivity table. I have user_id in useractivity table.

      when i used "join" between these to table i am getting sync error or circular reference error.


      (I need to create a "Pivot table" which displays the count of email_type_id in a column. which can be filtered on client basis.)

       

      Please anyone help on this where i need to add both the above columns and table.

       

      I think this is clear, if not please ask.

       

      Thanks,

      Pramod

        • Re: adding two columns
          Andy Weir

          I think you need to revisit your data model.

           

          Could you not have a single fact table with common activity information named the same e.g. email_type_id, action date etc.. and a type field where you id your activity type as either Sign Up or User with keys to link off to the user dimension and sign up dimension table where you store details unique to that dimension then these types of issues wont arise.

           

          Regards

           

           

           

          Andy

            • Re: adding two columns
              Kumar Pramod

              hi Andy Weir,

               

              Din't get you. would you explain it briefly.

               

              Thanks,

              Pramod

                • Re: adding two columns
                  Andy Weir

                  Why not have one table to store all your activities in with common attributes like %SiteId, %UserId, Date, Type of Action i.e. Signup, user visit etc....  With keys off to user details, site details etc... in a classic star schema that way you will avoid getting stuck on joins, circular references as your fact data is all in the one table.

                   

                  At the moment I think you storing values separately when they could be in the same field which you can get away with in SQL Relational DBs but Qlik Data model expects it in a certain way.

                   

                  referrer_email_type_id, email_type_id its just an email type so why not just store them in one table as email type and add a field to give it context email=referrer  

                   

                   

                  Use of set analysis in your measures will extract the relevant data for you in your charts.

                   

                  Hope this makes sense.

                   

                   

                  Andy

              • Re: adding two columns
                neetha P

                hi pramod,

                 

                rename columns to same name

                  • Re: adding two columns
                    Kumar Pramod

                    hi neetha,

                     

                    I renamed the column  to the same name the issue is when i load the useractivity table with fields user_id and "status" as email_type_id,  i am getting the sync error or circular reference error.


                    This is because email_type_id and user_id both is using for association. This is when i loaded without join.

                     

                    When i loaded with joining the two tables, i am only loading the "status" as email_type_id from useractivity table, at that time email_type_id is loading but all other fields are empty. this is affecting me when i was filtering through filter pane.

                     

                    I think you have understand the issue.

                     

                    Thanks,

                    Pramod

                      • Re: adding two columns
                        neetha P

                        please can you paste the code here

                          • Re: adding two columns
                            Kumar Pramod

                            Please check the code

                             

                            LOAD `user_id`,

                                client_id,

                                status as email_type_id;

                            SQL SELECT `user_id`,

                                 client_id,

                                 status

                            FROMUSERACTIVITY where status in (2,3,4,8);

                             

                            LOAD `invitation_id`,

                                `user_id`,

                                 client_id,

                                 batch_meta_data_id,

                                `referrer_email_type_id` as email_type_id ,

                                `user_signed` as signed;

                            SQL SELECT `invitation_id`,

                                `user_id`,

                                batch_meta_data_id,

                                client_id,

                                `referrer_email_type_id`,

                                `user_signed`

                            FROM SIGNUPACTIVITY;