Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Data separator by commas

Hello experts, I have in the data load of a txt file, the separation of the data is by commas but there is a problem with a data column.  This column has commas in the data, which is why some data is not shown in its  respective column. Is it possible to control this problem?  Thank you very much for your comments.

 

RUC, NAME

2 20522471101,YOKOGAWA AMERICA DO SUL, LTDA. SUCURSAL

 

 

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

If your file has only two columns and the first column will never have a comma you can fix it with something like this:

LOAD
    SubField(@1, ',' 1) as RUC
    Mid(@1, Index(@1, ',')+1) as NAME
FROM
    MyTextFile.txt
    (txt, utf8, no labels, delimiter is ',', msq)
    ;


In general if the text with the comma isn't surrounded with quotes in the text file then Qlikview/QlikSense can't distinguish between comma's that should be used as field separators and comma's that should be part of the text. In that case you should go back to the system that generated the text file and make sure that when the text file is generated the text is quoted or that a different field separator is used. Otherwise you'll have to go through the text file manually and fix the problematic records yourself.

If you have to do it manually you could check which records are problematic. You could find out which records have a problem by adding two columns, one for the record number and one error_check column that will only have a value if the record has a problem because there are extra commas leading to a string value ending up in the error_check column.

LOAD
    RecNo() as Record,
    RUC,
    NAME,
    Error_Check
FROM
    MyTextFile.txt
    (txt, utf8, no labels, delimiter is ',', msq)
     ;

You can then select all values in the Error_Check field to see which records have a problem

 

 


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

If your file has only two columns and the first column will never have a comma you can fix it with something like this:

LOAD
    SubField(@1, ',' 1) as RUC
    Mid(@1, Index(@1, ',')+1) as NAME
FROM
    MyTextFile.txt
    (txt, utf8, no labels, delimiter is ',', msq)
    ;


In general if the text with the comma isn't surrounded with quotes in the text file then Qlikview/QlikSense can't distinguish between comma's that should be used as field separators and comma's that should be part of the text. In that case you should go back to the system that generated the text file and make sure that when the text file is generated the text is quoted or that a different field separator is used. Otherwise you'll have to go through the text file manually and fix the problematic records yourself.

If you have to do it manually you could check which records are problematic. You could find out which records have a problem by adding two columns, one for the record number and one error_check column that will only have a value if the record has a problem because there are extra commas leading to a string value ending up in the error_check column.

LOAD
    RecNo() as Record,
    RUC,
    NAME,
    Error_Check
FROM
    MyTextFile.txt
    (txt, utf8, no labels, delimiter is ',', msq)
     ;

You can then select all values in the Error_Check field to see which records have a problem

 

 


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Creator II
Creator II

Excelent explanation my friend !!! You are genius