Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

2nd Left Join not working

Hi all,

Sorry I am new at transformation work.

I have been trying to get this 2nd join working for some time and have no idea why it isn't working (Qlik is not very helpful at diagnosing syntax errors!)

Everything in the Green runs perfectly - it's only when I add the last (red) statement that it doesn't run.

Also, when I comment out the applymap in the red section it still doesn't work.

I need these both joined to the FACT Table.

Would really appreciate any help.

Thanks

LIB CONNECT TO 'Verint Reporting DB';

FACTKPI:
LOAD ID as FACTKPIID,
DIMEMPLOYEEID,
    DIMKPIID,
    DIMPERIODICITYID,
    DIMORGANIZATIONID,
    DIMTIMEID,
    DIMSCOREID,
    DIMGOALID,
    DIMROLLUPTYPEID,
    DIMEDMID,
    ACTUALVALUE as KPIRESULT,
    ACTUALGOALVALUE as KPIGOAL,
    PEERVALUE as PEERBENCHMARKRESULT,
    PERCENTMET;
   
SQL SELECT ID,
DIMEMPLOYEEID,
    DIMKPIID,
    DIMPERIODICITYID,
    DIMORGANIZATIONID,
    DIMTIMEID,
    DIMSCOREID,
    DIMGOALID,
    DIMROLLUPTYPEID,
    DIMEDMID,
  ACTUALVALUE,
    ACTUALGOALVALUE,
    PEERVALUE,
    PERCENTMET
FROM BPWAREHOUSEDB.dbo.FACTKPI WHERE DIMORGANIZATIONID=13;

LEFT JOIN (FACTKPI)
LOAD ID as DIMEMPLOYEEID,
TRIM(FIRSTNAME&' '& LASTNAME) as EMPLOYEE;
SQL SELECT ID,
    FIRSTNAME,
    LASTNAME
FROM BPWAREHOUSEDB.dbo.DIMEMPLOYEE WHERE ISACTIVE='Y';

MSQUEUES:


MAPPING
    LOAD * INLINE [
    PILOT,     Value
    Existing Members,  MS
    MS Account Update,  MS
    MS Payment Update,  MS
    New Membership,   MS
    New Mbrship Campaign, MS
    New Business Sales,  MS
    Travel,     MS
    Practice Policy,  MS
    RRP,     MS
    Direct 1,    MS
    Direct 2,    MS
    Direct 3,    MS
    Direct 4,    MS
    Direct 5,    MS
];

LEFT JOIN (FACTKPI)
LOAD
ID as DIMEDMID,
NAME as PILOT;
ApplyMap('MSQUEUES', PILOT, 'Non-MS') as MSPILOT;
SQL SELECT ID,
NAME,
FROM BPWAREHOUSEDB.dbo.DIMEDM;

6 Replies
oknotsen
Master III
Master III

You have two semi-colons in the last table that should not be there.

Look behind PILOT and MSPILOT. Both should be commas.

Note that you should also give your Mapping table a name.

May you live in interesting times!
Anonymous
Not applicable
Author

Thanks!

Not applicable
Author

HI Peter

As mentioned above by Onno you need to give your mapping table a name so you can refer to it in your ApplyMap Function.

Apart from that,I think the problem is that you are referring to a non existing field in your ApplyMap fucntion.

You load ID and Name from SQL so there is no field named PILOT.

There was a semicolon right after PILOT where there had to be a comma.

Regards

Daan

MSQUEUES:

MAPPING LOAD * INLINE [

    PILOT,     Value

    Existing Members,  MS

    MS Account Update,  MS

    MS Payment Update,  MS

    New Membership,   MS

    New Mbrship Campaign, MS

    New Business Sales,  MS

    Travel,     MS

    Practice Policy,  MS

    RRP,     MS

    Direct 1,    MS

    Direct 2,    MS

    Direct 3,    MS

    Direct 4,    MS

    Direct 5,    MS

];

LEFT JOIN (FACTKPI)

LOAD

ID as DIMEDMID,

NAME as PILOT,

ApplyMap('MSQUEUES', NAME, 'Non-MS') as MSPILOT;

SQL SELECT ID,

NAME,

FROM BPWAREHOUSEDB.dbo.DIMEDM;

oknotsen
Master III
Master III

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
Anonymous
Not applicable
Author

Hi there

Sorry I don't seem to have a 'Correct Answer' button, at least not a big one! I must be blind?

Anonymous
Not applicable
Author

Hi Peter, you have to view the actual discussion thread in the community.

Here's a direct link: Re: 2nd Left Join not working

The Correct Answer button does not appear in the thread preview area within your profiles Inbox & Activity section. You can click on the blue title to go to the thread.

Best,

Qlik Community Team