topic Re: Extracting states from an address field in QlikView

Extracting states from an address field

Thu, 12 Jun 2014 11:07:22 GMT

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

Re: Extracting states from an address field

Thu, 12 Jun 2014 11:20:19 GMT

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"

];

Re: Extracting states from an address field

Anonymous — Thu, 12 Jun 2014 11:40:00 GMT

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

Re: Extracting states from an address field

Thu, 12 Jun 2014 11:57:57 GMT

Thankyou Uday!

I will apply this to my model

Greatly appreciated!!