Load Excel field; SQL Case statement equivalent

[#1]

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

[nagaiank · Accepted solution]

Do you really need two tables. You may load the termband in the same table as given below:

Directory;

test_table:

LOAD Leasenum,

Name,

Term,

If (Term <= 36, '0-36',

If (Term <= 48, '37-48', '48+')) as Termband,

[Total Financed $],

Date

FROM

testfile.xls

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