5 Replies Latest reply: May 10, 2012 12:20 PM by jmickelson RSS

    How to join a data file with .xls file - only the matching keys

      I have created a crosstable to bring in the data from my business system;

           GL-acct #, FiscalYear, FiscalPeriod, ActualAmt

      Now I want to use only certain GL-acct# so I created a xls file with GL-acct# and their description.  I am getting every ActualAmt for every FiscalPeriod tied to a couple of the GL-acct# (and description).  Below is the script I'm working with.

       

      CTActual:
      CrossTable(actual,aamt, 2)
      load
      "GL#1" & '-' & "GL#2" AS GLACCT,
      CCYY,
      TT01,
      TT02,
      TT03,
      TT04,
      TT05,
      TT06,
      TT07,
      TT08,
      TT09,
      TT10,
      TT11,
      TT12;

       

      SQL SELECT "GL#1",
      "GL#2",
      CCYY,
      TT01,
      TT02,
      TT03,
      TT04,
      TT05,
      TT06,
      TT07,
      TT08,
      TT09,
      TT10,
      TT11,
      TT12
      FROM SAS400C.GLMT where CCYY >= '2011' ;

       

      CTSlsMast:
      LOAD SGLACCT,
      SDESCRIP
      FROM [Sales-accts.xls]
      (biff, embedded labels, table is Sheet1$);
      Join
      Load GLACCT as SGLACCT,
      CCYY,
      actual,
      aamt,
      right(actual,2) as Period
      Resident CTActual
      ;

       

      Any help would be greatly appreciated!