topic Re: split a column in QlikView

split a column

jood_ahmad — Thu, 29 Dec 2016 12:05:39 GMT

Dears,

if i have in one var-char column these value

( -059;+308;+SFCRF0012;601;+671;671;024;023;043;061;033;059 )

( -664;-664;-664;101;+636;671;664;047;@101 )

( +204;601;671 )

and i need to split it to columns as per the number value after each ( ; ) as a new column is this possible in QV .

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:14:17 GMT

Yes, you can use subfield function

Re: split a column

jood_ahmad — Thu, 29 Dec 2016 12:22:45 GMT

but Subfield wil not work on this case. i need all the values but in separate columns. subfield will take one value as per the position.

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:29:26 GMT

use the subfield function by adding a expression and give this fieldname. it should work

Re: split a column

sunny_talwar — Thu, 29 Dec 2016 12:33:36 GMT

I think this is what pipuindia99‌ is trying to say

Table:

LOAD *,

SubField(FieldName, ';') as NewFieldName;

LOAD * Inline [

FieldName

-059;+308;+SFCRF0012;601;+671;671;024;023;043;061;033;059

-664;-664;-664;101;+636;671;664;047;@101

+204;601;671

];

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:36:52 GMT

Yes, but to get all we can give frequency for each

Re: split a column

sunny_talwar — Thu, 29 Dec 2016 12:39:10 GMT

Like this?

Table:

LOAD *,

SubStringCount(FieldName, NewFieldName) as Frequency;

LOAD *,

SubField(FieldName, ';') as NewFieldName;

LOAD * Inline [

FieldName

-059;+308;+SFCRF0012;601;+671;671;024;023;043;061;033;059

-664;-664;-664;101;+636;671;664;047;@101

+204;601;671

];

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:43:11 GMT

SubField(fieldname,'.','1') and in next expression SubField(fieldname,'.','2') .....

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:43:42 GMT

SubField(fieldname,';','1') and in next expression SubField(fieldname,';','2') .....

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:44:32 GMT

so each values would be in different columns...

Re: split a column

sunny_talwar — Thu, 29 Dec 2016 12:45:28 GMT

You want this in multiple columns and not a single row?

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:47:40 GMT

I think Ahmad Kastero is expecting like that...

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:48:06 GMT

I think Ahmad Kastero is expecting like that...

Re: split a column

sunny_talwar — Thu, 29 Dec 2016 12:49:21 GMT

Oh okay, for a moment I thought you were the one who asked the question. My apologies

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:50:37 GMT

Re: split a column

jood_ahmad — Thu, 29 Dec 2016 12:51:22 GMT

thank you but in this way i will have many records because the table that i will load from more than 3- million records

Re: split a column

sunny_talwar — Thu, 29 Dec 2016 12:52:26 GMT

Then how exactly you want to do this?

Re: split a column

pipuindia99 — Thu, 29 Dec 2016 12:53:07 GMT

I think you can do it in scripting itself, so you will get the values split

Re: split a column

Anil_Babu_Samineni — Thu, 29 Dec 2016 13:13:48 GMT

Jood_Ahmad

Is this you are expecting

Re: split a column

jood_ahmad — Thu, 29 Dec 2016 13:17:22 GMT

yes like this how can i get it.