7 Replies Latest reply: May 25, 2017 7:59 PM by Jeremy Latimer RSS

    2nd Left Join not working

    Peter Bower

      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;