Having Trouble Filling in Nulls

[bnelson111]

I have a left join table were 041 is the stat-code and the description should read 'deleted' as i want to replace the Null currently reading against the 041 code. But the table is coming up as pic see below script??

Script

left join

LOAD "stat-code",

if(len("stat-code")='041','deleted',description) as description,

    "type",

    cost,

    kco,

    movement,

    indirect,

    "trans-cost",

    "sundry-code",

    "restrict-flag",

    "sub-type",

    PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

[anbu1984]

if(Trim("stat-code")='041','deleted',description) as description,

[bnelson111]

unfortunately didn't work

left join

LOAD "stat-code",

if(Trim("stat-code")='041','deleted',description) as description,

//description,

    "type",

    cost,

    kco,

    movement,

    indirect,

    "trans-cost",

    "sundry-code",

    "restrict-flag",

    "sub-type",

    PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

//-------- End Multiple Select Statements ------

[Gysbert_Wassenaar]

The length of a value will never be '041'. So you probably mean if("stat-code"='041', ...etc.

Is the field description part of the left table or of the PUB.sfstatcode? If that field only exists in the left table then you first have to join the tables before you can fill the nulls.

[anbu1984]

Is stat-code join key?

[CELAMBARASAN]

May be stat-code = 041 exists only in the first table, which you do left join with

If that is the case then use the below code

Lets say first loaded table is Table1(before the left join)

left join

LOAD "stat-code",

     description,

    "type",

    cost,

    kco,

    movement,

    indirect,

    "trans-cost",

    "sundry-code",

    "restrict-flag",

    "sub-type",

    PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

Left Join(Table1)

LOAD DISTINCT "stat-code",

     if(Trim("stat-code")='041','deleted',description) as NewFieldDescription

Resident Table1;

[chrislofthouse]

Try this:

if(Trim([stat-code])=041,'deleted',description) as description,

Or if you can upload the app this would help diagnose the problem quicker.

[bnelson111]

Yes

[bnelson111]

Heres the two tables

[SFDC DATA]:

LOAD kco,

    kjobcode,

    kcompno,

    "seq-code",

    "mach-id",

    "stat-code",

    "op-code",

    "start-time",

   Interval#( "elapse-time",'s') as "Seconds",

   "elapse-time",

    "shift-code",

    "extract-flag",

    "non-prod-flag",

    "seq-code2",

    "overtime-flag",

    kprocno,

    "history-flag",

    formno,

    "event-type",

    "substat-init",

    "substat-final",

    date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY') as "End Date",

     day(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Day Completed",

     Month(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Month Completed",

      year(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Year Completed",

    amended,

    "ass-no",

    "group-code",

    "seq-num",

    "sub-type",

    "comment-flag",

    "group-ind",

    PlantCode,

    "event-qty",

    "misc-qty",

    "sundry-qty";

SQL SELECT *

FROM PUB.sfeventcds

WHERE "start-time" > '733682170';

left join

LOAD "stat-code",

description,

    "type",

    cost,

    kco,

    movement,

    indirect,

    "trans-cost",

    "sundry-code",

    "restrict-flag",

    "sub-type",

    PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

//-------- End Multiple Select Statements ------

[anbu1984]

As Celambarasan said below, you may not have 041 in PUB.sfstatcode. Try as he suggested below