Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have one query that I have one excel sheet USA
OrderID | LineNO | FormulaID | Country |
1 | 200 | AXY76 | UNITED STATE A |
2 | 300 | BHC98 | U.S.A |
3 | 500 | MM55 | U.STATE.A |
4 | 600 | OI11 | AMERICA |
5 | 400 | FR56 | US |
so I want in all country name as USA
Thanks and Regards,
PM
Hello
Take a look on the attached file.
Regards,
Gabriel
if(match(Country,'UNITED STATE A','U.S.A','U.STATE.A','AMERICA','US'),'USA',Country) as Country,
you can use a mapping table and ApplyMap or in the script use in the script if(wildmatch(country,'united states','u.s.a.',...)>0,'USA',country)
Hi,
Maintain one more excel which will contain the countryname ex :
Country | CountryName |
UNITED STATE A | USA |
U.S.A | USA |
U.STATE.A | USA |
AMERICA | USA |
US | USA |
then use below script:
tempcountry:
LOAD
OrderID,
LineNO,
FormulaID,
Country
FROM
[..\..\Calendar.xlsx]
(ooxml, embedded labels, table is Sheet2);
maptable:
MAPPING LOAD
Country,
CountryName
FROM
[..\..\Calendar.xlsx]
(ooxml, embedded labels, table is Sheet3);
Country:
LOAD
OrderID,
LineNO,
FormulaID,
ApplyMap('maptable',Country) as countryname
Resident tempcountry;
DROP Table tempcountry;
hope it helps
Regards,
Pooja
Hi Prashat,
use this calculated dimension(Expression)
=if(match(Country,'UNITEDSTATEOFAMERICA','U.S.A','AMERICA','U.STATE.A','US'),'USA',Country)
Here country is u r field name
'UNITEDSTATEOFAMERICA','U.S.A','AMERICA','U.STATE.A','US these are u r data in country filed.
i hope u understand otherwise please check the files.
Hi PM,
in Your Table You have only Field Country like U.S.A., America,.. or other Country ?
Thanks to all of you .
I will implement in my script and let you know......