5 Replies Latest reply: Dec 13, 2016 6:06 PM by James Hanify RSS

    Joining two sets of data together?

    James Hanify

      Hi all,

       

      Bit confused how I can do this, the name in particular is in two different dictionaries, but may not be in the same, for example DZI does not exist with heir name in Users, but is in Salesman, and this will incur duplicates if i just Concatenate them.

       

      Salesman

      SalesCodeKeySalesCodeNameUserKeySalesmanGroup
      W2Dones ZhiDZIA
      12Jimbo HanesJHAB

       

      User

       

      UserKeyUserName
      JHAJimbo Hanes
      RTORudolf Tones

       

      Combined:

       

      UserKeyUserNameSalesmanCodeKeySalesmanGroup
      DZIDones ZhiW2A
      JHAJimbo Hanes12B
      RTORudolf TonesNullNull

       

       

      Example Code:

       

      Users:
      load
      Z_ID as UserKey,
      NAME as UserName;
      
      SQL SELECT *
      FROM SY_OPERATOR;
      
      Salesman:
      load
      Z_ID as SalesCodeKey,
      USER_ID_C as UserKey,
      SALESM_NAME as Username,
      SALESMAN_GROUP_C as SalesmanGroup;
      
      SQL SELECT *
      FROM "SM_SALESMAN";
      
      

      Thanks,

       

      James

        • Re: Joining two sets of data together?
          Sunny Talwar

          May be this:

           

          Table:

          LOAD SalesCodeKey,

            SalesCodeName as UserName,

            UserKey,

            SalesmanGroup

          FROM [https://community.qlik.com/thread/242933]

          (html, codepage is 1252, embedded labels, table is @1);

           

          Join (Table)

          LOAD UserKey,

              UserName

          FROM

          [https://community.qlik.com/thread/242933]

          (html, codepage is 1252, embedded labels, table is @2);

          • Re: Joining two sets of data together?
            Vineeth Pujari

            Or Even Concatenate with NOT EXIST()

             

            Table:

            LOAD SalesCodeKey,

              SalesCodeName as UserName,

              UserKey,

              SalesmanGroup

            FROM [https://community.qlik.com/thread/242933]

            (html, codepage is 1252, embedded labels, table is @1);

             

            Concatenate

             

             

            LOAD UserKey,

                UserName

            FROM

            [https://community.qlik.com/thread/242933]

            (html, codepage is 1252, embedded labels, table is @2)

            WHERE NOT EXISTS(UserKey);

              • Re: Joining two sets of data together?
                James Hanify

                Many thanks both, i don't think it quite is what I need yet, this is the reason why I want to put it in the bigger User table, despite it looking like the child of it.

                 

                A salesperson may or may not be related to a User, a user may be deleted, in which case, the id needs to come from the salesperson table but there are more associative properties to the User but just not being used in this case.

                 

                When I try join, there were a few instances where the UserKey occured twice and on the concatenate, I couldn't get the exists to work with my SQL QV generator.

                  • Re: Joining two sets of data together?
                    Vineeth Pujari

                    A salesperson may or may not be related to a User,

                    What does this mean? can a salesperson  have 0 or Many UserKey?


                    perhaps a detailed sample data that exhibits the problem will help us help you

                      • Re: Joining two sets of data together?
                        James Hanify

                        Hi Vineeth, sorry for not getting back quickly. As part of a user clean up, which I didn't think was a good idea, but it was done anyway, was that some users are deleted from the system meaning that some of these salesman may exist as salesman but not as users, so therefore I was going to pull the name from the salesman record if the users didnt exist, however, I found another solution using the applymap idea.

                         

                        Here is a question though, i've been using applynames for simple tables that are key, description as i didn't see the point in linking a table just for that data, if there was more than that I would. This helped when you had multiple going to the same key, however, what do you do if you have to go to that key multiple times? I was going to use the key as another name but then it creates them junction object syns, is there any other way around it, i've just another applymap but there is some structure that may be helpful for me to have.