Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Thanks. Will test in the morning.
Do I not need to load Item2 in the Concatenate load statement?
Thank ylu.
That worked fine. Thank you.
For future readers, I would add that you also need to drop Table A at the
end.