10 Replies Latest reply: Apr 19, 2016 9:52 AM by Jeroen Bergmans RSS

    Applymap show no values

    Jeroen Bergmans

      Hi,

       

      I am using ApplyMap as a vlookup to 3 side tables for my main table. This works for 2 of them.

      For the other one, where I try to get numeric values it seems to be more difficult to get it working.

      To explain what I am doing here. I have an excelsheet  matrix which I crosstable to a reusable

      format with 3 columns, I recreate a sheet with 2 columns from where I want to have the 2nd column

      in my main table. I am trying to get this value with applymap (ApplyMap('FXRatesFinal',FXtoEurPair_plus_Dates) as FXToEUR;)

      in a preceding LOAD.

       

      Is someone able to help me out here?


      FXRates:
      CROSSTABLE(FXtoEurPair,FXRate)
      LOAD
      Dates,
          EURCAD,
          EURSEK,
          EUREUR,
          EURDKK,
          EURNOK,
          EURAUD,
          EURCHF,
      EURUSD,
          EURGBP,
          EURBRL,
          EURCOP,
          EURHUF,
          EURIDR,
          EURINR,
          EURJPY,
          EURMXN,
          EURMYR,
          EURNGN,
      EURPEN,
          EURPHP,
          EURPLN,
          EURROB,
          EURSGD,
          EURTHB,
          EURTRY,
          EURZAR,
          EURKRW,
          EURRUB,
          EURRON
         
      FROM [lib://BrokerTurnover/FXsheet.xlsx]
      (ooxml, embedded labels, table is [FX to EUR QLIK]);

       

      FXRatesFinal:
      LOAD
      FXtoEurPair & '-' & Dates as FXtoEurPair_plus_Dates,
      FXRate

      RESIDENT FXRates;


      DROP TABLE FXRates;

       

      Trades:

      / create "PRECEDING LOADS" for FXtoEurPair_plus_Dates etc om de apply map in de volgende load te gebruiken

       

      LOAD *,ApplyMap('FXRatesFinal',FXtoEurPair_plus_Dates) as FXToEUR;

      LOAD *,'EUR'& "Asset Currency"&'-'& "TradeDate" as FXtoEurPair_plus_Dates;

       

      LOAD
          "Acct Code",
          "Acct Name",
          Isin,
          CadisCode,
          BloombergCode,
          CrdCode,
          Ticker,
          "Security Name",
          Status,
          OrderID,
          TradeID

      FROM [lib://BrokerTurnover/All_Trades.xlsx]
      (ooxml, embedded labels, table is All_Trades);