If field contains change all records on load

[#1]

Hi Experts

(sorry for so many posts)

On load I have 4 columns ,

Name, Food , Type, Eaten ,

I am looking to add another column for [Have I eaten Fruit] and calculate the results based on Name ,Food , Type, Eaten, -- so if Anne has eaten Fruit once , all data entries for Anne Have I eaten Fruit will change to YES. See sample with desired results below .

Please help :

Name	Food	Type	Eaten	Have I eaten Fruit
Anne	Bacon	Meat	Y	Yes
Anne	Apple	Fruit	Y	Yes
Anne	Sausage	Meat	N	Yes
Sean	Bacon	Meat	Y	No
Sean	Beef	Meat	Y	No
John	Apple	Fruit	N	No
John	Bacon	Meat	Y	No
John	Beef	Meat	Y	No
Adam	Apple	Fruit	Y	Yes
Adam	Bacon	Meat	Y	Yes

[#2]

Hi,

I would do the following, it is surely not the simplest way.

1) You load the first 4 fields into a temp table

2) You load a mapping table from that

My_Map:

MAPPING LOAD Name, 'YES' as Flag

RESIDENT temp_Table

WHERE Type = 'Fruit' AND Eaten = 'Y'

3) I load the definitive table from the temp one and the mapping

LOAD *,

  Applymap('My_Map', Name, 'NO') as 'have I Eaten Fruit'

RESIDENT temp_Table

The last arg of the ApplyMap is the de fault value that should be NO. We have populated the mapping only with those who have eaten fruits.

Fabrice

[matthias_v]

LEFT JOIN (OrigTable)

LOAD DISTINCT

Name,

'YES'                as [Have I Eaten Fruit]

RESIDENT Table

where Type='Fruit' AND Eaten = 'Y';

Perhaps an easier approach. Do be careful with this one, since the combination of LEFT JOIN (Table) and LOAD DISTINCT, will also make your original table distinct. To avoid this you can first load the table as a temp table and afterwards write a left join to the original table resident the temptable. Well.. like so:

tt_FruitEaten:

LOAD DISTINCT

Name,

'YES'                as [Have I Eaten Fruit]

RESIDENT Table

where Type='Fruit' AND Eaten = 'Y';

LEFT JOIN (OrigTable)

LOAD

*

RESIDENT tt_FruitEaten;

DROP TABLE tt_FruitEaten;

Afterwards you can load your original table again and with a simple if specify that all values for the field [Have I Eaten Fruit] that equal null() should be filled with 'NO' otherwise keep the original value.

[#4]

Both solutions work

Say the field was [what was eaten] and I wanted the resut to be either Fruit/Meat/Both do I need to do the joins 3 times to make that happen ?

Name	Food	Type	Eaten	what was eaten
Anne	Bacon	Meat	Y	Both
Anne	Apple	Fruit	Y	Both
Anne	Sausage	Meat	N	Both
Sean	Bacon	Meat	Y	Meat
Sean	Beef	Meat	Y	Meat
John	Apple	Fruit	N	Meat
John	Bacon	Meat	Y	Meat
John	Beef	Meat	Y	Meat
Adam	Apple	Fruit	Y	Fruit
Adam	Pear	Fruit	Y	Fruit

[matthias_v]

I guess in this case i would use an aggregation to solve the puzzle:

LEFT JOIN (OrigTable)

LOAD

Name,

if(Count(DISTINCT Type) = 2, 'Both', only(Type))     as [What was eaten]

RESIDENT OrigTable

GROUP BY Name;