topic Re: How to separate comma delimited field during load in QlikView

How to separate comma delimited field during load

Thu, 16 Jun 2011 22:29:23 GMT

I have data in TXT FILE . It contains two field OBJECT AND VALUES. The Values field is always comma delimited as seen below in Current Structure.

I would like to change the table structure as shown in desired structure.

Can someone please help me how to achieve this during load??

Thank you.

Pranita

Current structure

OBJECT	VALUES
A	123,456,789
B	123,7888,9998
C	123232323,232323,08309273
D	456,7888,232323

Desired Structure:

OBJECT	VALUES
A	123
A	456
A	789
B	123
B	7888
B	9998
C	123232323
C	232323
C	08309273
D	456
D	7888
D	232323
D	23232389

How to separate comma delimited field during load

Thu, 16 Jun 2011 23:07:39 GMT

Hi Praneetha

Can u give me any sample file ...

How to separate comma delimited field during load

Thu, 16 Jun 2011 23:11:29 GMT

with this function: subfield( your fieldOBJECT ,',')

e.g.

LOAD

[OBJECT,

trim(subfield([Values],';')) AS [Project Ref. ProjectId TEMP]

RESIDENT Issue_Category

How to separate comma delimited field during load

erichshiino — Fri, 17 Jun 2011 01:18:24 GMT

Hi, you can use iterno() to run to a single line many times.

You can use subfield to retrieve part of the string separeted by the commas:

TABLE:

LOAD * INLINE [

OBJECT, VALUE

A, "123,456"

B, 1234

C, "136,1532,16323"

]

;

RESULT:

lOAD OBJECT, SubField(VALUE,',',ITERNO()) AS VALUE_RESULT

RESIDENT TABLE

WHILE(ITERNO()<=SubStringCount(VALUE,',')+1);

DROP TABLE TABLE;

Hope this helps,

Erich

How to separate comma delimited field during load

Fri, 17 Jun 2011 01:56:12 GMT

Hi,

Erich is correct, Subfield will do the job for you. But Iterno() function is not mandatory. If you use subfield at back end with out any third parameter will loop through the field.

TABLE:
LOAD *, SUBFIELD(VALUE,',') AS NEWVAULE;
LOAD * INLINE [
    OBJECT, VALUE
    A, "123,456"
    B, 1234
    C, "136,1532,16323"
];

Hope this may help you.

- Sridhar

How to separate comma delimited field during load

Fri, 17 Jun 2011 02:38:51 GMT

Thank you all for the quick reponse!!

Erich, Thank you for the solution. Worked perfectly!!

Re: How to separate comma delimited field during load

dileep — Mon, 26 Aug 2019 10:18:35 GMT

Hi Erich,

Your post was very help full thanks.

Dileep