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: 
shane_spencer
Specialist
Specialist

Issue with loading special character

I am trying to load a txt file but it appears to have a special character similar to a carriage return. I've tried using KeepChar to get rid of it but cannot seem to do anything.

As you can see I have opened it in Notepad and there's just two rows but if I open in Notepad ++ then the [Material Description] suddenly goes down a line as if there's carriage returns above and below. QlikView has the same issue.

sales.PNG

The question is how do I load this file correctly? PFA 

1 Solution

Accepted Solutions
marcus_sommer

IMO it's a serious issue within the dataquality of your source and if possible it should be corrected by the creation of the data or at least during the output from there (probably any kind of database).

Nevertheless most of such faulty data could with more or less efforts be corrected. Quite common for it is the use of vba/vbs but it's also possible within Qlik for example with an approach like this one:

m: mapping load repeat(chr(32), recno() + 1) as Lookup, chr(32) as Return autogenerate 40;

t1:
load concat(ValuesMerge, chr(10), RecNo) as ValuesMerge;
load *, replace(replace(ValuesMerge, ' |', '|'), '| ', '|') as ValuesMerge;
load *, mapsubstring('m', mid(ValuesMerge, 2, len(ValuesMerge)-2)) as ValuesMerge where Filter = 1;
load *, if(Check < previous(Check), 0, 1) as Filter, if(Check < previous(Check), peek('ValuesMerge' & Values), Values) as ValuesMerge;
load *, substringcount(Values, '|') as Check;
load [@1:n] as Values, recno() as RecNo
from [C:\Users\Marcus Sommer\Downloads\2018_11_SALES_XX.txt] (fix, codepage is 1252)
where not match(recno(), 1, 3);

t2:
load * from_field (t, ValuesMerge) (txt, utf8, embedded labels, delimiter is '|');

which detects the erroneous records with a substringcount() of the delimiter-char and put then the parts together again and concat it to get a single value on which a from_field load could be applied (the mapping and replace and so on are additionally just to clean the data a bit more).

- Marcus

View solution in original post

6 Replies
marcus_sommer

IMO it's a serious issue within the dataquality of your source and if possible it should be corrected by the creation of the data or at least during the output from there (probably any kind of database).

Nevertheless most of such faulty data could with more or less efforts be corrected. Quite common for it is the use of vba/vbs but it's also possible within Qlik for example with an approach like this one:

m: mapping load repeat(chr(32), recno() + 1) as Lookup, chr(32) as Return autogenerate 40;

t1:
load concat(ValuesMerge, chr(10), RecNo) as ValuesMerge;
load *, replace(replace(ValuesMerge, ' |', '|'), '| ', '|') as ValuesMerge;
load *, mapsubstring('m', mid(ValuesMerge, 2, len(ValuesMerge)-2)) as ValuesMerge where Filter = 1;
load *, if(Check < previous(Check), 0, 1) as Filter, if(Check < previous(Check), peek('ValuesMerge' & Values), Values) as ValuesMerge;
load *, substringcount(Values, '|') as Check;
load [@1:n] as Values, recno() as RecNo
from [C:\Users\Marcus Sommer\Downloads\2018_11_SALES_XX.txt] (fix, codepage is 1252)
where not match(recno(), 1, 3);

t2:
load * from_field (t, ValuesMerge) (txt, utf8, embedded labels, delimiter is '|');

which detects the erroneous records with a substringcount() of the delimiter-char and put then the parts together again and concat it to get a single value on which a from_field load could be applied (the mapping and replace and so on are additionally just to clean the data a bit more).

- Marcus

shane_spencer
Specialist
Specialist
Author

Thanks Marcus, that's really helpful! I suspected that data was bobbar but I'd hoped for an easy solution (like an option when using the LOAD wizard) but this will help me go back to the client to look at the source data before resorting to coding around the issue.

shane_spencer
Specialist
Specialist
Author

Hi Marcus, what does the mapping load / applymap do? I can't quite work that bit out.

marcus_sommer

A mapping with applymap() worked quite similar to the VLOOKUP function from Excel. It searched within the first column of the mapping table (which had always only two columns) for the search-value and returned the value from the first matching (from the second column) and in cases of non-matching it returns the third or if none is defined the second function-parameter as default-value.

This is very simple and also very fast - and if you need more you could nest multiple ones and/or concat the return values and splitting them again - and even this is usually quite fast and most often the best approach compared to other transformations, like joins.

The mapping with mapsubstring() is similar with the difference that the return-value replaced the search-value within the field-value.

Further information and background will you find here: Mapping-and-not-the-geographical-kind

- Marcus

shane_spencer
Specialist
Specialist
Author

Thanks Marcus - I was familiar with ApplyMap but not mapsubstring().

I think I understand that in the above example you are replacing a long string of "spaces" with a single space right?

marcus_sommer

Yes, exactly.