13 Replies Latest reply: Jun 20, 2013 3:06 AM by PAUL YEO RSS

    Link table creation

      Hello,

       

      I want to know how to create link table using script.

      Please find the attached file for more information.

       

      Thanks

      Samir

        • Link table creation
          Sravan Puppala

          Hi see this link

           

          http://community.qlik.com/message/36977#36977

           

          it might help you.

           

          regards

          sravan

            • Re: Link table creation
              Sravan Puppala

              User:

              Noconcatenate

              Load autonumber(LoginId & Server) as LoginIDServer,

                      LoginId,

                      Server,

                      FullName,

                      Emailid

              ;

               

              LinkTable:

              Load Distinct LoginIDServer,

                      LoginId,

                      Server,

              Resident User;

               

              Drop tables LoginId, Server from User;

               

              Project:

              Noconcatenate

              Load  autonumber(Server & Project_Path) as ServerProject_Path,

              Project_Path,

              Project_Title,

              Project_Created_On,

              Server

              ;

               

              Join(LinkTable)

              Load Distinct ServerProject_Path

              Project_Path,

              Server

              Resident Project;

               

              Drop Fields Project_Path,Server from Project

              ;

               

              LoginDetails:

              Noconcatenate

              Load autonumber(LoginId & Server) as LoginIDServer,

                      autonumber(Server & Project_Path) as ServerProject_Path,

              LoginId,

              Server,

              Project_Path,

              Access_On,

              ;

               

              Join(LinkTable)

              LoginIDServer,

              ServerProject_Path,

              LoginId,

              Server

              Resident LoginDetails

              ;

               

              Drop tables LoginId,Server from LoginDetails;

               


               

              This must solve your problem..Notice it is untested...

              instead of join may be you have to use concatenate depending on the data!

               

              Regards

              Sravan

                • Link table creation

                  Hello Sravan,

                   

                  Heartiest thanks for quick response.

                   

                  I tried this but still it is creating one synthetic key (LoginIDServer+ServerProject_Path) and as my data too big, getting failure.

                   

                  I guessed why it is creating synthetic key: In LinkTable table, after first two join, there are 5 fields:

                  LoginIDServer,

                  ServerProject_Path,

                  LoginId,

                  Server,

                  Project_Path

                   

                  As we go further to join the LoginIDServer, ServerProject_Path from LoginDetails table to LinkTable table, it creates synthetic key.

                   

                  Can you please guide me how to remove this synthetic key?

                   

                  Thanks

                  Samir

                    • Link table creation

                      Hello Samir,

                      QV links tables together using same fieldnames. This is a very powerful and convenient feature of QV but it may also result into funny and unexpected results (data-structures like snyc-tables) if you are not familar with it. So the first try would be to use the qualify - statement before loading the seond table. Or rename the according fields, if you do not want the links.

                      Second is to look where it is necessary to link tables via keys together. It can be very useful to link tables, best with only one corresponding keyfield in each table.

                      HtH for the moment

                      Roland

                        • Re: Link table creation
                          Sravan Puppala

                          Hi Samir,

                           

                          I think what you need is one more combined Key...

                          User:

                          Noconcatenate

                          Load autonumber(LoginId & Server) as LoginIDServer,

                                  LoginId,

                                  Server,

                                  FullName,

                                  Emailid

                          ;

                           

                          LinkTable:

                          Load Distinct LoginIDServer,

                                  LoginId,

                                  Server,

                          Resident User;

                           

                          Drop fields LoginId, Server from User;

                           

                          Project:

                          Noconcatenate

                          Load  autonumber(Server & Project_Path) as ServerProject_Path,

                          Project_Path,

                          Project_Title,

                          Project_Created_On,

                          Server

                          ;

                           

                          Join(LinkTable)

                          Load Distinct ServerProject_Path

                          Project_Path,

                          Server

                          Resident Project;

                           

                          Drop Fields Project_Path,Server from Project

                          ;

                           

                          LoginDetails:

                          Noconcatenate

                          Load autonumber(LoginId & Server & Project_Path) as LoginIDServerProjectPath,

                                  autonumber(LoginId & Server) as LoginIDServer,

                                  autonumber(Server & Project_Path) as ServerProject_Path,

                          LoginId,

                          Server,

                          Project_Path,

                          Access_On,

                          ;

                           

                          Join(LinkTable) distinct

                          LoginIDServerProjectPath,

                          LoginIDServer,

                          ServerProject_Path,

                          LoginId,

                          Server

                          Resident LoginDetails

                          ;

                           

                          Drop Fields LoginIDServer,ServerProject_Path,LoginId,Server from LoginDetails;



                          If It still does not help, add an example!

                           

                          Regards

                          Sravan

                            • Re: Link table creation
                              Sravan Puppala

                              Hey samir,

                               

                              Did u solve your Problem??

                               

                              Test by debugging with less no of records..

                              • Link table creation

                                Hi Sravan,

                                 

                                I am getting two different value for 'LoginIDServer' for the same LoginId & Server in LinkTable

                                 

                                LoginID_Server_1.png

                                LoginID_Server_2.png

                                 

                                 

                                Thanks

                                ___________________________________________________________________________

                                 

                                User:

                                Noconcatenate

                                Load autonumber(LoginId & Server) as LoginIDServer,

                                        LoginId,

                                        Server,

                                        FullName,

                                        Emailid

                                ;

                                 

                                LoginDetails:

                                Noconcatenate

                                Load autonumber(LoginId & Server & Project_Path) as LoginIDServerProjectPath,

                                        autonumber(LoginId & Server) as LoginIDServer,

                                        autonumber(Server & Project_Path) as ServerProject_Path,

                                LoginId,

                                Server,

                                Project_Path,

                                Access_On,

                                ;

                                 

                                LinkTable:

                                Load Distinct LoginIDServer,

                                        LoginId,

                                        Server,

                                Resident User;

                                 

                                Drop fields LoginId, Server from User;

                                ***********************************************************************************************

                                 

                                Join(LinkTable) distinct

                                LoginIDServerProjectPath,

                                LoginIDServer,

                                ServerProject_Path,

                                LoginId,

                                Server

                                Resident LoginDetails

                                ;

                                 

                                Drop Fields LoginIDServer,ServerProject_Path,LoginId,Server from LoginDetails;

                                ______________________________________________________________________________________

                                  • Link table creation

                                    Hello,

                                     

                                    I am generating each table using the script in seperate qvw file & storing the output into seperate QVD file.

                                     

                                    Thanks

                                    Samir

                                      • Link table creation
                                        Sravan Puppala

                                        Hi Guess,

                                         

                                        sorry for the delay in answering.Autonumber generates a automatic number and I am assuming it might be the cause or may be not. Try this

                                        and if it still does not work. Add an example.

                                         

                                         

                                        User:

                                        Noconcatenate

                                        Load LoginId & '_' & Server) as LoginIDServer,

                                                LoginId,

                                                Server,

                                                FullName,

                                                Emailid

                                        ;

                                         

                                        LoginDetails:

                                        Noconcatenate

                                        Load LoginId & '_' & Server & '_' & Project_Path as LoginIDServerProjectPath,

                                                LoginId & '_' & Server as LoginIDServer,

                                                Server & '_' & Project_Path as ServerProject_Path,

                                        LoginId,

                                        Server,

                                        Project_Path,

                                        Access_On,

                                        ;

                                         

                                        LinkTable:

                                        Load Distinct LoginIDServer,

                                                LoginId,

                                                Server,

                                        Resident User;

                                         

                                        Drop fields LoginId, Server from User;

                                        ***********************************************************************************************

                                         

                                        Join(LinkTable) distinct

                                        LoginIDServerProjectPath,

                                        LoginIDServer,

                                        ServerProject_Path,

                                        LoginId,

                                        Server

                                        Resident LoginDetails

                                        ;

                                         

                                        Drop Fields LoginIDServer,ServerProject_Path,LoginId,Server from LoginDetails;

                                        Hope it helps

                                        Sravan