topic Joining two fields (Left Join OR Left Join) in QlikView

Joining two fields (Left Join OR Left Join)

Thu, 14 Aug 2014 08:37:24 GMT

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.

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

Gysbert_Wassenaar — Thu, 14 Aug 2014 12:44:20 GMT

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);

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

Thu, 14 Aug 2014 13:04:26 GMT

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 — Thu, 14 Aug 2014 13:29:35 GMT

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

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

Thu, 14 Aug 2014 13:36:15 GMT

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