topic Re: Issue with loading special character in QlikView

Issue with loading special character

shane_spencer — Fri, 21 Dec 2018 14:44:56 GMT

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.

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

Re: Issue with loading special character

marcus_sommer — Sat, 22 Dec 2018 13:09:11 GMT

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

Re: Issue with loading special character

shane_spencer — Thu, 27 Dec 2018 08:26:22 GMT

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.

Re: Issue with loading special character

shane_spencer — Wed, 23 Jan 2019 10:18:55 GMT

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

Re: Issue with loading special character

marcus_sommer — Wed, 23 Jan 2019 12:52:53 GMT

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

Re: Issue with loading special character

shane_spencer — Wed, 23 Jan 2019 15:15:02 GMT

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?

Re: Issue with loading special character

marcus_sommer — Wed, 23 Jan 2019 15:31:01 GMT

Yes, exactly.