topic Re: How to split column value to multiple column values in straight table in QlikView

How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 12:43:22 GMT

Hi,

I have one address field

for eg: 12 Oak Tree Drive,Cave Hollod, Hadgoed ,Mid Glamorgan , , CF22 8TN

I need to split the above address as

Address1 = 12 Oak Tree Drive

Address2 = Cave Hollod

Address3 = Hadgoed

Town = Mid Glamorgan

County = ' '

PostCode = CF22 8TN

Could anyone help me please how to write the expressions in Straight table.

Thanks.

Re: How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 14:14:16 GMT

Use subfield:

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Examples:

(For three parameters)

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ';' ,1) returns NULL if S is an empty string

subfield(S, ';' ,1) returns an empty string if S is ';'

The LOAD statement will look like this:

LOAD

subfield(@1, ',', 1) as Address1

subfield(@1, ',', 2) as Address2

subfield(@1, ',', 3) as Address3

subfield(@1, ',', 4) as Town

subfield(@1, ',', 5) as County

subfield(@1, ',', 6) as Postcode

FROM

Be aware that the comma can only be used as a delimiter.

Re: How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 14:32:27 GMT

Thanks for your reply could you let me know in the subfield where can I use the columnname . The column is Home_address

Re: How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 14:44:14 GMT

I used @1 for the columnname.

So replacing the first parameter in the subfield-statement by Home_address is what you're looking for.

Re: How to split column value to multiple column values in straight table

MayilVahanan — Mon, 23 Sep 2013 15:14:08 GMT

LOAD

subfield(Home_address, ',', 1) as Address1

subfield(Home_address, ',', 2) as Address2

subfield(Home_address, ',', 3) as Address3

subfield(Home_address, ',', 4) as Town

subfield(Home_address, ',', 5) as County

subfield(Home_address, ',', 6) as Postcode

FROM TableName

Re: How to split column value to multiple column values in straight table

jagan — Mon, 23 Sep 2013 15:47:59 GMT

Hi,

Try like this

LOAD

subfield(Home_address, ',', 1) as Address1

subfield(Home_address, ',', 2) as Address2

subfield(Home_address, ',', 3) as Address3

subfield(Home_address, ',', 4) as Town

subfield(Home_address, ',', 5) as County

subfield(Home_address, ',', 6) as Postcode

FROM TableName;

Regards,

Jagan.

Re: How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 16:10:18 GMT

Thanks for your help but in the blank values it is showing Town,County and Postcode. Could you let me know how to adjust this please.

Re: How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 17:33:01 GMT

Thanks for your help but in the blank values it is showing again the same problem. could you please help

Re: How to split column value to multiple column values in straight table

Mon, 23 Sep 2013 17:34:02 GMT

Thanks and sorry it's getting the same way as earlier. could you please help.

Re: How to split column value to multiple column values in straight table

MayilVahanan — Mon, 23 Sep 2013 18:09:59 GMT

Can you please attach sample