Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to divide column value to multiple column values


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

10 Replies
Highlighted
Partner
Partner

Re: How to divide column value to multiple column values

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,

Highlighted
Partner
Partner

Re: How to divide column value to multiple column values

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.

Highlighted
Creator
Creator

Re: How to divide column value to multiple column values

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

Highlighted
Creator III
Creator III

Re: How to divide column value to multiple column values

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: How to divide column value to multiple column values


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.

Highlighted
Not applicable

Re: How to divide column value to multiple column values

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

Highlighted
Not applicable

Re: How to divide column value to multiple column values

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: How to divide column value to multiple column values

Hi,

Can you attach some sample data?

Regards,

Jagan..

Highlighted
Not applicable

Re: How to divide column value to multiple column values

Hi,

Please find sample Qvw and please help me.

Thanks.