Derive additional column on existing table during Load

[#1]

Hi!

Is there a way I can derive an additional field on existing table during the LOAD?

Sample Output:

ID	Status	Final Status
Abc	pass	Passed
Def	pass (with special cases)	Passed
Ghi	fail	Failed

Final Status column will be my additional/derive field based on the initial Status field on my existing table.

Appreciate your help.

-Bea

[stigchel · Accepted solution]

You can do a load from an existing table using resident, if you want add (join) the column to the existing table use a join load. Something like:

Join (SourceData) LOAD

ID,

If(left(Status,4)='pass','Passed',If(left(Status,4)='fail','Failed')) as [Final Status]

Resident SourceData;

[Ralf-Narfeldt]

Add a preceding load that adds the Final Status:

Load *, If(left(Status,4) = 'fail', 'Failed', 'Passed') As [Final Status];

Load * inline [

ID,Status

Abc,pass

Def,pass (with special cases)

Ghi,fail];

This is based on your input data, if you have more possible values, you may need to adjust the If condition.

[#4]

Works perfectly! Thanks!