1 Reply Latest reply: Jul 10, 2011 6:54 PM by Nagaian Krishnamoorthy RSS

    Load Excel field; SQL Case statement equivalent

      Reposted from "Community" site to "Developer" site:

       

      Hello,

       

      I normally load directly from an SQL server and have been adding fields using a Case statement in the script.  For example:

       

      Datatable:

       

      Select

      Term

      WHEN Term is null then 'No Term'

      WHEN Term <=12 then '0-12'

      WHEN Term >=13 and Term <=24 then '13-24'

      WHEN Term >=25 and Term <=36 then '25-36'

      ELSE '37+'

      End as term_band

       

       

      I would like to create the same type of field when loading from an excel file.  Something like this:

       

      Directory;

      test_table:

      LOAD Leasenum,

      Name,

      Term,

      [Total Financed $],

      Date

      FROM

      testfile.xls

      (biff, embedded labels, table is Sheet1$);

      Band_dim:

      Load

      [Term],

      If [Term] <= 36 then "0-36"

      elseif 37 < [Term] <= 48 then "37-48"

      Else "48+" as [Termband]

      Endif

      Resident test_table;

       

       

      Thanks,

       

      Paul