Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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$);
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$);