topic Re: How to split column in to two columns in QlikView

How to split column in to two columns

Tue, 21 Jan 2014 14:46:09 GMT

Hi,

I have Address column like below

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP

How can I get 32 Beach Drive, Scratby, Great mouth, folk, UK in one column and UY29 3NP in another column.

can anyone help me please.

Thanks.

Re: How to split column in to two columns

jagannalla — Tue, 21 Jan 2014 14:48:30 GMT

Hi,

Load SubField(Address,',') as Address from table;

Sorry I think you need to us if condition.

Load

If (Address ='UY29' or Address='3NP',Address) as Col1,

If (Address <> 'UY29' and Address <> '3NP',Address) as Col2

from table;

Cheers!!

Jagan

Re: How to split column in to two columns

marcus_malinow — Tue, 21 Jan 2014 14:51:53 GMT

Hi Amelia,

you could try these two expressions:

Left(Field, index(Field, ',',substringcount( Field, ',')) - 1)

mid(Field, index(Field, ',',substringcount( Field, ',')) + 1)

Marcus

Re: How to split column in to two columns

Tue, 21 Jan 2014 14:56:01 GMT

Hi Amelia, Nagan

Subfield is OK but you have to specify the position of the comma you want to split.

If no position, default is 0 and will split as 32 Beach Drive only

So you need to split

SubField(Address,',', 5) to get the last field

to get the whole other part, you may trick with

left(your-string, len(your_string - len(SubField(Address,',', 5)-2))

len(SubField(Address,',', 5)-2)) this will gives you the len to remove from the original string

best regards

chris

Re: How to split column in to two columns

jagan — Tue, 21 Jan 2014 15:02:04 GMT

Hi,

Try like this

For the first string

=Left('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', index('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', ', ', 5) -1)

For the second string

SubField('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', ', ', 6)

Regards,

Jagan.

Re: How to split column in to two columns

MayilVahanan — Tue, 21 Jan 2014 15:03:03 GMT

Try like this

SubField('32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP', ',',-1)

SubField(Address,',', -1)

Re: How to split column in to two columns

alexandros17 — Tue, 21 Jan 2014 15:05:58 GMT

This is a general solution, hope it helps

LOAD * INLINE [

NUM,Address

1,"32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP"

2,"42 Beach Drive, Scratby, Great mouth, folk, aaa, UK, vvvv"

3,"52 Beach Drive, Scratby, Great mouth, folk, bbb, ccc, UK, kkkk"

4,"62 Beach Drive, Scratby, Great mouth, folk, UK, zzzz"

];

EVAL:

NoConcatenate

LOAD *,

Left(Address, FindOneOf(Address, ',', SubStringCount(Address, ','))-1) as FirstPart,

Mid(Address, FindOneOf(Address, ',', SubStringCount(Address, ','))+1) as SecondPart

Resident Data;

DROP Table Data;

Re: How to split column in to two columns

rustyfishbones — Tue, 21 Jan 2014 15:33:08 GMT

try

Re: How to split column in to two columns

Tue, 21 Jan 2014 16:32:28 GMT

Hi, Thanks very much.

I can able to get this.

Re: How to split column in to two columns

alexandros17 — Tue, 21 Jan 2014 16:33:54 GMT

Hi Amelia,

please copy my example, it works and is a general solution...

Let me know

Re: How to split column in to two columns

Tue, 21 Jan 2014 16:40:07 GMT

Hi, Thanks,

Could you please help me as I am trying to get the Address as parts

I,e could I get te second part and third part separately,

i,e Scartby as one column

Great nouth as another column .

Please help me this is the one I need to sort out please.

Re: How to split column in to two columns

marcus_malinow — Tue, 21 Jan 2014 16:45:29 GMT

Hi Amelia,

probably the easiest solution is to use SubField.

For example SubField(Address, ',' 1) will retrieve the first part, SubField(Address, ',' 2) the second, etc...

You may wish to include an If condition around each so that if the subfield doesn't exist it doesn't error.

e.g.

If (SubstringCount(Address,',')>=1, SubField(Address, ',' 1), Null()) as FirstPart,

If (SubstringCount(Address,',')>=2, SubField(Address, ',' 2), Null()) as SecondPart,

etc...

Marcus

Re: How to split column in to two columns

rustyfishbones — Tue, 21 Jan 2014 16:49:09 GMT

Amelia,

Just say SUBFIELD(Address,',',) to get them all separted

Re: How to split column in to two columns

Tue, 21 Jan 2014 16:50:47 GMT

Hi,

thanks.

I tried this but it is showing error at '1' place.

Please help me how to set this as I am really struggling this to get this done since days.

Re: How to split column in to two columns

Tue, 21 Jan 2014 16:52:11 GMT

Thanks and I used the SUBFIELD and it showing values incorrect order
Is there any other way to get this. Please this becomes now as big task for me. please help.

Re: How to split column in to two columns

marcus_malinow — Tue, 21 Jan 2014 17:01:37 GMT

Aah, a couple of missing commas

If (SubstringCount(Address,',')>=1, SubField(Address, ',', 1), Null()) as FirstPart,

If (SubstringCount(Address,',')>=2, SubField(Address, ',', 2), Null()) as SecondPart,

Re: How to split column in to two columns

Wed, 22 Jan 2014 09:58:15 GMT

Thanks and I used the same and it's showing data when there is no data in second place. I need to have blank values if there is no data and values if there is data exists.

Please could help me how to set this.

Re: How to split column in to two columns

rustyfishbones — Wed, 22 Jan 2014 10:12:46 GMT

If there are blank values or no data, these need to be separated by a comma, otherwise it won't work.

so if the data is like

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP (this will be fine)

if it's like this

32 Beach Drive, , , , UK, UY29 3NP (it will also be fine)

however, if other addresses are like this

32 Beach Drive,folk, UK, UY29 3NP (it will not be fine)

you need to understand the Address field data first before you decide what function will be required to return the correct result

Re: How to split column in to two columns

Wed, 22 Jan 2014 10:43:42 GMT

Thanks and yes you are right I have most of the data as below

32 Beach Drive,folk, UK, UY29 3NP

so, in thus case how can I set this with out any issues. please help me.

Re: How to split column in to two columns

rustyfishbones — Wed, 22 Jan 2014 10:59:27 GMT

Where is the Source Data coming from?

Excel, Access, or a Database?