Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

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

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

Tags (2)
4 Replies
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand
Not applicable

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?

MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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