create new fields based on combination of existing data, BUT NOT USING IF

[mazacini]

Item	Type	Area	Rep		NewRep
1234	TypeA	North	John		John
1235	TypeA	South	Frank		Frank
1236	TypeB	East	Sarah		Sarah
1237	TypeC	North	John		John
1238	TypeC	East	Sarah		Sarah
1239	TypeD	North	John		Frank
1240	TypeB	North	John		Sarah

I have a table (Table A) with fields Item, Type, Area and Rep.

Item is a unique ID.

I want to append a new field NewRep, based on following rules:

1. If Type B is sold in North, Change Rep to Sarah;

2. Change Type D to Frank;

3, All Others - New Rep same as Rep;

I do not want to use an if statement in the load as there are actually more rules applying to the actual data.

I have tried to create a new table with the new field based on resident loads with "Where" conditions for Rules 1 & 2. But I am trying to figure out how to load all other data NOT covered by Rules 1 & 2.

Script (rough synatax) -

TableB:

Load *,

'Sarah as NewRep

resident TableA

where Type = TypeB and Area  = North;

Load *,

'Frank' as NewRep

resident TableA

where Type = TypeD;

This is where I have a problem

My idea was to use the Item field to identify which records had not yet been appended to Table B

So the script would do the following:

Where the Item has not been loaded to TableB,, load the records from TableA. and create the NewRep field using the Rep field for these records.

Any ideas?

[swuehl · Accepted solution]

If you want to do it that way maybe like

TableB:

Load *,

'Sarah as NewRep,

Item as Item2

resident TableA

where Type = TypeB and Area  = North;

Load *,

'Frank' as NewRep,

Item as Item2

resident TableA

where Type = TypeD;

Concatenate LOAD *,

Rep as NewRep,

Resident TableA

Where not exists(Item2, Item);

drop field Item2;

[mazacini]

Thanks. Will test in the morning.

Do I not need to load Item2 in the Concatenate load statement?

Thank ylu.

[mazacini]

That worked fine. Thank you.

For future readers, I would add that you also need to drop Table A at the

end.