Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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




1 Solution

Accepted Solutions
erichshiino
Partner - Master
Partner - Master

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

View solution in original post

6 Replies
Not applicable
Author

Hi Praneetha

Can u give me any sample file ...

Not applicable
Author

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

e.g.

LOAD

                                        [OBJECT,

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

                              RESIDENT Issue_Category

erichshiino
Partner - Master
Partner - Master

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
Author

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
Author

Thank you all for the quick reponse!!

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

dileep
Contributor
Contributor

Hi Erich,

Your post was very help full thanks. 

 

Dileep