How to divide column value to multiple column values

[#1]

Hi,

I have one column Adress as Apt,34,Rt1 7Yt,Uk

How to get Adrress filed divided as

Add1= Apt,

Add2= '  '

Add3 ='  '

Addr4= 34

Addr5= '  '

Addr6=Rt1 7YT

Addr7= UK

Could anyone help me as I used Subfield to get this but its not showing correct results.

Thanks

[jpapador]

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,

[christian77]

Hi.

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?

luck.

[Anonymous]

Hi Amelia,

have you tried subfield? It returns the n word, that is delimited by the comma. Example:

=SubField( 'Apt,34,Rt1 7Yt,Uk', ',', 1 )

Returns Apt

=SubField( 'Apt,34,Rt1 7Yt,Uk', ',', 2 )

Returns 34

Then you could do that in the Script, as:;

SubField( 'Apt,34,Rt1 7Yt,Uk', ',', 1 ) as Add1

hope I could Help

[Anonymous]

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

[jagan]

Hi,

Try like this

Years:

LOAD
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
INLINE [
Data
"Apt,34,Rt1 7Yt,Uk"
];

Hope this helps you.

Regards,

Jagan.

[#7]

Thanks ad what If I need to do if it contains blank values as I need to get in straight table

Add1=Apt

Add2=

Addr3=

Addr4=34

Addr5=

Addr6=Rt1 7YT

Addr7=UK

[#8]

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.

[jagan]

Hi,

Can you attach some sample data?

Regards,

Jagan..

[#10]

Hi,

Please find sample Qvw and please help me.

Thanks.