LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi Experts,

I am stuck and need your assistance. I need to pull specific value from a cell depend upon the pattern of the required data. Also, the referring column (Country) for pattern checking is different. Depend upon the Country column the match should check particular pattern

The reference sheet.

Country Postcodepattern MinLen MaxLen
UK ([a-z]){1,2}([0-9]){1,2} 5 9
USA [0-9]{5}|[0-9]{9} 5 9

Data:

Unit 1 Sample Drive, W3 3AZ, UK
123 Flower shop, 12345,Near Sample Shop

Desire Output:

Output
Unit 1 Sample Drive, UK
123 Flower shop, Near Sample Shop

Postcode(new column)
W3 3AZ
12345


Please let me know if you need further information.

Regards,
Jagdev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: AdamParker

Assuming you have the Country in a variable called _country, you could use a series of variables with switches to get the pattern you need, like below:

Edit: Modified the UK pattern based on PostCodes.brg.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gmullin

The attached graph is not so elegant but should do 2 independent checks for UK and USA. I changed your regex code for the UK as it wasn't picking up the blank space in the postcodes correctly.

I keep on repeating the same logic on each path, so this could be easily done in one path, with maybe UK and USA checks in a list (just a thought).

PostCodes.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi All,

Thanks for sharing your input on this. I will test the graph with my live data and will get back to you.

Regards,
Jagdev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi All,
The code looks fine and it is pulling out the pattern wise zip code from the addresses. Now I am thinking is there any way I can remove city and state from the address. I am thinking of reference sheet where I will be having list of state and city available within a country. I am not able to connect the logic through which I can refer the reference sheet with the name available in a address
Example:-
Reference sheet –
Country State City
USA Alabama Autaugaville
USA Alabama Bethel Grove
USA Alaska Akutan
USA Alaska Belkofski
UK Sample State Sample City

Data
Unit 1 Sample Drive, Sample City, Sample State W3 3AZ
123 Flower shop, 12345, Near Sample Shop, Alabama, Autaugaville
Country (Column in the data set)
Uk
USA
We have a country column as well which we can use as a reference column to check the country and then perform the address operation.
Regards,
Jagdev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlam

I've attached an example where you can loop through your reference table of country, state and city values.

Make sure your data is trimmed and I'd also recommend that you convert the reference and data to the same case before the lookup (you can do it within the logic of the lookup but I just think it makes it more cluttered).

This can easily get more complicated depending on the quality of your data - spelling mistakes is the first one the come to mind.

Alternatively, if your address data is in a relatively consistent format, it might be better to first delimit your address data (by spaces/commas etc) to get each element in a different column and then join on reference data simply using X-Ref.

Look_up_reference_list.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi Experts,

Thanks for the above graph. It works fine for the small list of data. In my case the reference data is in thousand like 70k. To follow the above logic it gets hang and at times the output is blank. Is there any alternative way to deal with this issue. Please help me with this.

Example

RAW Data
Unit 123-4, Sample abdcent, ABC Airport, Autaugaville,W3 3AZ,Alabama,USA

Output
City State Zip Country Address
Autaugaville Alabama W3 3AZ USA Unit 123-4, Sample abdcent, ABC Airport


Regards,
Jagdev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi Experts,

Any views on this please.

Regards,
Jagdev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Are you saying you have 70k addresses in a single field, or do you have 70k records, each with one address?

If there is more than one address in a singe field, the first thing I'd try to do is break them out into separate records.

If the logic is reading say 10k records and then failing for some reason, that's a different problem.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi Smith,

We have a file which contains the data and one/single address filled in a single cell. Every single cell has 1 address in it and we need to split the address into multiple column depends upon the string/value available in the reference sheet. The reference sheet is huge in volume i.e. over 70k. This reference sheet needs to be referred while checking the words/strings in the cell and upon finding the words need to be split in the respective column as per mentioned above.

Please let me know if you need further clarification.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

Hi Jagdev,

I have looked at the 'Look_up_reference_list.brg' data flow posted by mlam and I have made some adjustments to the code in the Lookup node's Script property [on the Output tab] to try to improve its efficiency.

I may have missed something w.r.t. the overall logic of your use case so I have added comments to highlight the changes and commented out the original lines where necessary.

I have profiled the node using a lookup data set of about 54k locations and it seems to be faster in my tests.

Look_up_reference_list_AW--share.brg

Regards,
Adrian

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Jagdev

Hi Adrian,

My location count will be minimal that is around 5k the max but, my reference sheet/data count would be over 300k. That is causing an issue it seems. Could we do something to get it sorted out. I think, since the number in the reference sheet is huge the Agg ex node is giving me blank result. Is there anyway to deal with such a huge data in the reference sheet.

Regards,
Jagdev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

Hi Jagdev,

I think you need to use a 'divide and conquer' approach rather than just a brute force approach. Rather than attempting to concatenate 300k reference locations into a single reference field/list.
I think you should consider splitting the reference locations into a number of subsets and then processing the location data across multiple lookup nodes.
I assume that the locations in the reference data are unique so there will only ever be one match from one of the lookup nodes. The processed data could then be concatenated using a Cat node.
It would also be best if you could split the location data by some pre-processing mechanism to reduce the number of records presented to each lookup node.
For instance you may want to have separate lookup nodes for USA and UK, and then split the location data on a coarse basis so that you steered the appropriate location records to the specified lookup node.
This would also introduce some parallelization as the current approach is single-threaded.

It would also be wise to introduce a Data Engineering approach, for instance: some sort of 'smart' ordering to the lookup data, e.g. order the locations, say, by city population descending so that they appeared earlier in the iteration loop and - meaning that a match would be found earlier for more frequently used locations.

Regards,
Adrian

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

As an example of how you might achieve the above, here is a modified data flow. This is not a generic solution for your use case as it only incorporates logic for US locations - you will need to adapt the logic to match your specific data.

In the data flow I have included reference data for US locations and some dummy location data are constructed. The location data contains addresses from a range of states.

Considering the Reference data: This is split into four subsets - in this case based on the first letter of the State field. A range of the first letters is associated with each output to split the reference data roughly into quarters (its not exact since the split is relatively coarse and all locations in a particular state will be switched to the specified output).

Considering the Location data: This is also split into four subsets - again, using the first letter of the 'state' element within the Data field.
*Note:* It is important that the criteria used to split the Reference Data and the Locations Data match - else you will steer location records to Lookup nodes that do not have corresponding Reference data, leading to unmatched locations.

Considering the Lookup step: The Lookup nodes were tweaked to only output records that matched (i.e. where _matchFound == true)

Finally, the results from each of the Lookup steps are concatenated to form the final output data.

Look_up_reference_list_AW_with_Split_Lookup--share.brg

Regards,
Adrian