I hope you Have Party Name wise Correct data in following way:
Party Name Correct City Correct State
So Use apply Map Two Times As Below:
[Correct City ]
[Correct State ]
Use apply Map into your base table two times one for city and one for state
ApplyMap('CityMapping','[Party ID]',City) as CorrectCity,
ApplyMap('StateMapping','[Party ID]',State) as CorrectState
Thanks for reply, i have data like this:-
factory party_name city state region country continent G a SALEM GUJARAT WEST INDIA ASIA G b COMMON KARNATAKA SOUTH INDIA ASIA G c BANGALORE DELHI SOUTH INDIA ASIA G d AHMEDABAD GUJARAT SOUTH INDIA ASIA G e DELHI COMMON SOUTH INDIA EUROPE G f BANGALORE KARNATAKA SOUTH INDIA ASIA G g BANGALORE KARNATAKA SOUTH INDIA ASIA
but i dont have correct data with me that i shown in blue
You can use DynamicUpdateCommand in Macro like this
SET Result = ActiveDocument.DynamicUpdateCommand ("UPDATE Table SET city = 'BANGALORE' WHERE factory='G' and party_name='b' ")
This Macro works in Ajax Mode as well.
You can link this Trigger on OnOpen Doc or Botton Action.
1317041.qvw 154.8 K
Please find the below output.
LOAD * INLINE [
factory, party_name, city, state, region, country, continent
G, a, SALEM, GUJARAT, WEST, INDIA, ASIA
G, b, COMMON, KARNATAKA, SOUTH, INDIA, ASIA
G, c, BANGALORE, DELHI, SOUTH, INDIA, ASIA
G, d, AHMEDABAD, GUJARAT, SOUTH, INDIA, ASIA
G, e, DELHI, COMMON, SOUTH, INDIA, EUROPE
G, f, BANGALORE, KARNATAKA, SOUTH, INDIA, ASIA
G, g, BANGALORE, KARNATAKA, SOUTH, INDIA, ASIA
NoConcatenate LOAD factory,party_name,region,country,
If(state ='KARNATAKA', 'BANGALORE',If(state ='DELHI', 'DELHI', city)) as city,
If(city ='DELHI', 'DELHI', state) as state,
If(city ='DELHI', 'ASIA', continent) as continent Resident Master;
DROP TABLE Master
This may be a fix as of now. but the permanent fix is to create a master table and Implement Left join or Applymap in it.
still i can see Salem is tagged to Gujarat instead of Ahmadabad. and we cannot assume that either the state or city has a wrong mapping unless and untill we have a Master table(or File)