Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

How to separate comma delimited field during load

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

OBJECTVALUES
A123,456,789
B123,7888,9998
C123232323,232323,08309273
D456,7888,232323

Desired Structure:

OBJECTVALUES
A123
A456
A789
B123
B7888
B9998
C123232323
C232323
C08309273
D456
D7888
D232323
D23232389




Tags (3)
1 Solution

Accepted Solutions
Partner
Partner

How to separate comma delimited field during load

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

6 Replies
Not applicable

How to separate comma delimited field during load

Hi Praneetha

Can u give me any sample file ...

Not applicable

How to separate comma delimited field during load

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

e.g.

LOAD

                                        [OBJECT,

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

                              RESIDENT Issue_Category

Partner
Partner

How to separate comma delimited field during load

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

Not applicable

How to separate comma delimited field during load

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

Not applicable

How to separate comma delimited field during load

Thank you all for the quick reponse!!

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

dileep
New Contributor

Re: How to separate comma delimited field during load

Hi Erich,

Your post was very help full thanks. 

 

Dileep