Getting parts of the contact's adress like city, street etc (Qlik Web Connector, Outlook 365)

[DatinexPeyman]

Hello dear qlikers,

i read the contacts from outlook 365 using "qlik web connectors". For adresses like business adress or private adress i get all the parts as a concatenated comma seperated list. From MS documentaion i know that the adresses have the following structure:

{
"city": "string",
"countryOrRegion": "string",
"postalCode": "string",
"state": "string",
"street": "string"
}

Is there anyway to get this parts separatly? Or determine what is the postal code in this string?
Many thanks for any idea in advance!

Best Regards
Peyman

[Jay_Brown]

Hello Peyman,

Retrieving only postalCode is not available at the connector level but you should be able to use Resident Load to work around this:

let vQwcConnectionName = 'lib://OutlookConnector';

Test01:
LOAD
displayName as Contacts_displayName,
businessAddress as Address
FROM [$(vQwcConnectionName)]
(URL IS <your webconnectors url, etc>, qvx);

ResTest:
Load *,
SubField(Address,',',1) as Street,
SubField(Address,',',2) as City,
SubField(Address,',',3) as State,
SubField(Address,',',4) as Country,
SubField(Address,',',5) as PostalCode
Resident Test01;

DROP Table Test01;

Note that this may require some manual standardization of your contacts, I had some other country codes that needed to be changed.

Does this help?

Best regards,

--Jay

[DatinexPeyman]

Hello Jay,

thank you for you answer.

It is not the solution, becuse if you have no Street in your Address, the City would be the first part of the Address and so on ...

Wish you a nice Qliking time 😉

Regards

Peyman