topic Re: Splitting the words in a field in QlikView

Splitting the words in a field

Tue, 04 Mar 2014 04:21:38 GMT

I am having a qvd file having field name colors. This field is consist of accumulated records of color names

example RE, BLRE.. where BL and RE is seperate color names. I want to split these names into another record and store in a temp .

Also i want to generate unique color ID for the new born records.

rwunderlich — Tue, 04 Mar 2014 05:07:03 GMT

1. Are all the color names two characters?

2. Are all the possible color names known in advance?

-Rob

tresB — Tue, 04 Mar 2014 06:21:12 GMT

Probably you need Subfield() function in combination with another string fucntion Left(). Try something like:

Load

Subfield( Left(COLORS,2)& ';' &Mid(COLORS,2) , ';' ) as NewCOLORS

Tue, 04 Mar 2014 06:31:18 GMT

Yes. Color Names are tow characters concatenated within one field (column)

And all the color names are known to me, I just want to split the word in two characters.

Thank you in advance

Tue, 04 Mar 2014 06:41:57 GMT

Expected Result was

BUDPLPORVI into BU

Source Code:

TEMP_COLOR:

LOAD CODE,

COLORS

Resident INVENTOR_QVD

Where COLORS <> '';

LOAD

Subfield( Left(COLORS,2)& ';' &Mid(COLORS,2) , ';' ) as NewCOLORS

Resident TEMP_COLOR;

Drop Table TEMP_COLOR;

tresB — Tue, 04 Mar 2014 07:01:27 GMT

Well now the requirement is clearer. Try like:

Load

Mid(COLORS, (Iterno()-1)*2+1, 2) as NewCOLORS

From <>

While Len(COLORS)>=IterNo()*2;

Tue, 04 Mar 2014 07:20:19 GMT

Thank You Very Much. Its really working