I have one column Adress as Apt,34,Rt1 7Yt,Uk
How to get Adrress filed divided as
Add2= ' '
Add3 =' '
Addr5= ' '
Could anyone help me as I used Subfield to get this but its not showing correct results.
This could be achieved in 2 steps. I am not sure this is the best way but this is the way I would do it.
I would use the subfield command to break apart the field into as many new columns as values (it looks like you would need 7).
Then I would search those new columns using WildMatch and then place them into columns that I want. An example is below.
SubField(Adress,',',1) as NewColumn1,
SubField([Adress,',',2) as NewColumn2,
SubField(Adress,',',3) as NewColumn3,
SubField(Adress,',',4) as NewColumn4,
Then search for each individual new column you want and name the column what you are looking for.
If(WildMatch(NewColumn1, '*Apt*') > 0, NewColumn1,
If(WildMatch(NewColumn2, '*Apt*') > 0, NewColumn2,
If(WildMatch(NewColumn3, '*Apt*') > 0, NewColumn3,
If(WildMatch(NewColumn4, '*Apt*') > 0,NewColumn4)))) as Add1,
They are more string funcktions.
SubstringCount, and FindOneOf could help you.
Try nesting funcktions.
Anyway, it's so difficult to manage open text fields.
adresses are dificult since ones have apartment, others have rooms, other live in single houses with no apartments, etc.
I think the solution is outside the funcktions. Are your adresses from UK only?
have you tried subfield? It returns the n word, that is delimited by the comma. Example:
=SubField( 'Apt,34,Rt1 7Yt,Uk', ',', 1 )
=SubField( 'Apt,34,Rt1 7Yt,Uk', ',', 2 )
Then you could do that in the Script, as:;
SubField( 'Apt,34,Rt1 7Yt,Uk', ',', 1 ) as Add1
hope I could Help
You could do something like this...
Add1 = subfield(Address,',',1)
Add2 = ' '
Add3 = ' '
Add4 = subfield(Address,',',2)
Add5 = ' '
Add6 = subfield(Address,',',3)
Add7 = subfield(Address,',',4)
or you could use textbetween() function if the data field is formatted the same for each value
Try like this
SubField(Data, ',', 1) AS Add1,
'' AS Add2,
'' AS Add3,
SubField(Data, ',', 2) AS Add4,
'' AS Add5,
SubField(Data, ',', 3) AS Add6,
SubField(Data, ',', 4) AS Add7
Hope this helps you.
Thanks ad what If I need to do if it contains blank values as I need to get in straight table
Thanks and I used the same function but getting values in place of blank values and no values in place of values. could you help me how to sort this problem.