Connectivity & Data Prep

[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