Hi everyone
I need help on the below case
my primary data consists of around 29 fields and about 500K rows.
For simplicity i have created an example inline table with only 4 columns
mainData:
LOAD * INLINE [Status, Country, Location, Result
Active, USA, Area1, N/A
Inactive, USA, Area2, N/A
Inactive, Canada, Area1, N/A
Active, Canada, Area2, N/A
Unknown, Italy, Area3, N/A
Active, Germany, Area5, N/A
Active, Germany, Area2, N/A
];
I have many additional tables that have 4 or less columns
modifyData1:
LOAD * INLINE [newStatus, newCountry, newLocation, newResult
Active, USA, Area1, Good
Active, USA, Area2, Bad
Active Canada, Area2, Average
Active, Germany Area2, Excellent
];
(i might have another modifyData2 table where newstatus=Inactive and so on)
I want to update the mainData table such that
if newstatus=Status AND NewCountry=Country AND newLocation=Location then
replace the N/A value with the newResult value
So after running the update my original data should look like this
Active, USA, Area1, Good
Inactive, USA, Area2, N/A
Inactive, Canada, Area1, N/A
Active, Canada, Area2, Average
Unknown, Italy, Area3, N/A
Active, Germany, Area5, N/A
Active, Germany, Area2, Excellent
(note that not necesseraly all values of modifyData1 and mainData should match)
the closes i got to this idea was mapping table and applymap but i need to match multiple fields to make the change.
Any help is greatly appreciated
thank you all
