topic Re: How to divide column value to multiple column values in QlikView

How to divide column value to multiple column values

Wed, 02 Oct 2013 14:07:24 GMT

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

Re: How to divide column value to multiple column values

jpapador — Wed, 02 Oct 2013 14:13:38 GMT

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,

Re: How to divide column value to multiple column values

christian77 — Wed, 02 Oct 2013 14:15:28 GMT

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.

Re: How to divide column value to multiple column values

Anonymous — Wed, 02 Oct 2013 14:17:42 GMT

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

Re: How to divide column value to multiple column values

Anonymous — Wed, 02 Oct 2013 14:18:21 GMT

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

Re: How to divide column value to multiple column values

jagan — Wed, 02 Oct 2013 14:21:06 GMT

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.

Re: How to divide column value to multiple column values

Wed, 02 Oct 2013 14:50:58 GMT

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

Re: How to divide column value to multiple column values

Wed, 02 Oct 2013 14:52:07 GMT

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.

Re: How to divide column value to multiple column values

jagan — Wed, 02 Oct 2013 17:12:33 GMT

Hi,

Can you attach some sample data?

Regards,

Jagan..

Re: How to divide column value to multiple column values

Thu, 03 Oct 2013 10:42:05 GMT

Hi,

Please find sample Qvw and please help me.

Thanks.

Re: Re: How to divide column value to multiple column values

christian77 — Thu, 03 Oct 2013 11:30:48 GMT

Hi Amelia.

I’ve seen your file. As I said before, open text fields are very difficult to manage.

I asked you a question. Are they all addresses from the UK? Let’s assume that, so they will all have the same format.

The format is supposed to have 5 fields separated by a coma. For some reason not all of them come correctly written.

Many are missing. If the Street is missing, we can’t do absolutely anything but inventing data. So discard those with the street missing. Other fields can be uncompleted, but not the street. Imagine somebody tells you, we’ll meet tomorrow at ‘no street’. Would you go to the appointment?

House or Street Numbers can be identified cause they’re numbers.

Since it can be a London area and a London street, we can’t do absolutely anything.

The postal code or ZIP, has a clear format. That’s CAP CAP NUM SPACE CAP NUM NUM. If you have a map of ZIP – TOWN, you can figure out the town for every zip. You may wan’t to have a street mapping but street names are repeated in different towns. Look for the funcktion mapping. They are web pages showing all this maps and they are easy to read with QlikView itself.

So, with all this reasoning, get to your help page, look for string funcktions and construct your script.

If you use a chart, you must set it up to see everything. In your chart, null areas don’t show anything. Add a dimension like street.

You can add those comas missing and then reload the table again.

Good luck.