4 Replies Latest reply: Aug 14, 2014 9:36 AM by Cheyne Ravenscroft RSS

    Joining two fields (Left Join OR Left Join)

      HI,

       

      I have been having problem creating a join on my data, i have the following script at the moment.

       

      RawTelephoneData:

      LOAD [Call Direction],

           Date,

           Time,

           From AS OutboundExt,

           To AS InboundExt,

           Duration,

           Cost,

           [Inbound Number]

      FROM

      [C:\Users\Cheyne\Documents\RawTelephoneData.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      LEFT JOIN (RawTelephoneData)

      LOAD StatusUpdateUserID,

           Extension AS OutboundExt

      FROM

      [C:\Users\Cheyne\Documents\ExtensionTranslation.xlsx]

      (ooxml, embedded labels, table is Sheet2);

       

       

      LEFT JOIN (RawTelephoneData)

      LOAD StatusUpdateUserID,

           Extension AS InboundExt

      FROM

      [C:\Users\Cheyne\Documents\ExtensionTranslation.xlsx]

      (ooxml, embedded labels, table is Sheet2);

       

      So the extension Translation Document Looks like this for instance

       

      User ID     Extension

      1               Ext: 200

       

      The Raw Telephone Data has the following Columns

       

      Date                    Time          From             To                    Duration     Cost

      01/01/2000         12:00:00     Ext:200           0700000000      1:00          0.01  

      02/01/2000         12:00:00     07000000000    Ext:200           1:00          0.01  

       

      My desired outcome is to have the user id joined every time Ext: 200 is in the From OR To Column, currently with my script it only works from the OutboundExt (From) Column.

       

      Any help would be much appreciated.

        • Re: Joining two fields (Left Join OR Left Join)
          Gysbert Wassenaar

          The problem is that after the first join the result table contains StatusUpdateUserID. So the second join uses this field too for the joining instead of just InboundExt as you probably expect.

           

          Try using a mapping table and the applymap function:

           

          MapUser:

          MAPPING LOAD StatusUpdateUserID, Extension

          FROM [C:\Users\Cheyne\Documents\ExtensionTranslation.xlsx]

          (ooxml, embedded labels, table is Sheet2);

           

          RawTelephoneData:

          LOAD [Call Direction],

               Date,

               Time,

               From AS OutboundExt,

               applymap('MapUser',From,null()) as StatusUpdateUserIDFrom,

               To AS InboundExt,

               applymap('MapUser',To,null()) as StatusUpdateUserIDTo,

               Duration,

               Cost,

               [Inbound Number]

          FROM

          [C:\Users\Cheyne\Documents\RawTelephoneData.xlsx]

          (ooxml, embedded labels, table is Sheet1);

            • Re: Joining two fields (Left Join OR Left Join)

              Thank you for the reply That works perfectly, how would i track back to a UserTable from here?

               

              if in my table i have StatusUpdateUserIDTo and StatusUpdateUserIDFrom, how can i like that to a table that has UserID and a FullName for the user for instance? Or would having separate user tables for inbound and outbound records be a better idea?

                • Re: Re: Joining two fields (Left Join OR Left Join)
                  Gysbert Wassenaar

                  You can do several things. Usually the userid is not interesting for the analyst using the qlikview document. You could map the user name instead of the id number. Perhaps that is enough to satisfy the requirements.

                   

                  Or perhaps you need to split the records so you get one field with the userid that can be associated with your user table. Something like:

                   

                  MapUser:
                  MAPPING LOAD StatusUpdateUserID, Extension
                  FROM [C:\Users\Cheyne\Documents\ExtensionTranslation.xlsx]
                  (ooxml, embedded labels, table is Sheet2);
                  
                  CallDetails:
                  LOAD
                      autonumberhash128([Call Direction],Date,Time,From) as CallKey,
                      Duration,
                      Cost,
                      ....etc
                  FROM ....
                  
                  CallUser:
                  LOAD
                      autonumberhash128([Call Direction],Date,Time,From) as CallKey,
                      'From' as Role,
                      From as Extension,
                      applymap('MapUser',From,null()) as UserID
                  FROM ....
                  
                  Concatenate(CallUser)
                  LOAD
                      autonumberhash128([Call Direction],Date,Time,From) as CallKey,
                      'To' as Role,
                      To as Extension,
                      applymap('MapUser',To,null()) as UserID
                  FROM ....
                  
                  Users:
                  LOAD
                      UserID,
                      UserName,
                      ....
                  FROM ...etc