Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have an address field and in order to categorise the states into a region, I need to extract the specific 3 digit state code.
addresses are pretty standard i.e. P. Sherman 42 Wallaby Way, Sydney, NSW, 2000 and I am looking to get the state code.
I'm and Aussie so we only have the following state and territory codes required.
ACT, NSW, QLD, TAS, WA, SA, NT, VIC
The problem is that none of the addresses have a standard delimiter, nor position within the data for the state code and some of them are missing altogether (I would use an NA result for those if possible)
Can you please have a think and let me know if this is possible? I am investigating substring functions and will post an answer if I can come up with one!
Oh and this needs to be in the script so I can use an applymap function to categorise them
StateMap:
Mapping
LOAD * INLINE [
StateCode, State
0,NA
1, ACT
2, NSW
3, QLD
4, TAS
5, WA
6, SA
7, NT
8, VIC
];
Load *, ApplyMap('StateMap',Statecode) as State;
Load Address,WildMatch(Address,'*ACT*', '*NSW*', '*QLD*', '*TAS*', '*WA*', '*SA*', '*NT*', '*VIC*') as Statecode;
LOAD * INLINE [
Address
"P. Sherman 42 Wallaby Way, Sydney, NSW, 2000"
"P. Sherman 42 Wallaby Way, Sydney, QLD, 2000"
"P. Sherman 42 Wallaby Way, Sydney TAS 2000"
"WA 3000"
"XXXX YYYYY"
];
StateMap:
Mapping
LOAD * INLINE [
StateCode, State
0,NA
1, ACT
2, NSW
3, QLD
4, TAS
5, WA
6, SA
7, NT
8, VIC
];
Load *, ApplyMap('StateMap',Statecode) as State;
Load Address,WildMatch(Address,'*ACT*', '*NSW*', '*QLD*', '*TAS*', '*WA*', '*SA*', '*NT*', '*VIC*') as Statecode;
LOAD * INLINE [
Address
"P. Sherman 42 Wallaby Way, Sydney, NSW, 2000"
"P. Sherman 42 Wallaby Way, Sydney, QLD, 2000"
"P. Sherman 42 Wallaby Way, Sydney TAS 2000"
"WA 3000"
"XXXX YYYYY"
];
Hi,
Use the mid function.
Example:
I have the date like 01-Feb-2014
now my requirement is to get only Feb as output for that I'll written the condition like
Mid(Date,3,3) -> O/p is Feb
Thankyou Uday!
I will apply this to my model
Greatly appreciated!!