Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
Thanks!
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;
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 .
Hi there
Sorry I don't seem to have a 'Correct Answer' button, at least not a big one! I must be blind?
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