Joining two fields (Left Join OR Left Join)

[#1]

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

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN (RawTelephoneData)

LOAD StatusUpdateUserID,

     Extension AS OutboundExt

FROM

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN (RawTelephoneData)

LOAD StatusUpdateUserID,

     Extension AS InboundExt

FROM

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

[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

(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

(ooxml, embedded labels, table is Sheet1);

[#3]

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?

[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 
(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

[#5]

My concern with mapping the UserName was that there may be instances of two users with the same name on our system, UserID is always a unique field. Going to have a play around with it, thanks for all your help