Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

datanibbler
Esteemed Contributor

"Foreign" text in a field

Hi,

I have an issue in a subroutine used to identify whether records are clean or have to be processed.

One part of that routine is identifying "foreign" text - any text that should not be there.

For that, I use the following code (in a LOAD):

>>> LEN(PurgeChar(Mat_pre, ' /-0123456789STUECKstueckÜüPp.')) <<

That covers all possible variations of 'Stueck' (or 'Stück' or 'Stk' or even 'pcs')

The issue is: I have records in those Excel_lists (exported from a customer_tool) where there are three lines in one (like you can do in Excel by hitting "Alt+Enter").

Usually then there is text in all of those lines. Sometimes, there is not - strangely, there are two line_breaks in a cell at times, with only one of those three "virtual" lines filles.

However, that should not be an issue - my code should be able to capture that.

What irritates me is that in two of those cases - where I have two linebreaks in one cell, but actually no text (except the word 'pcs' in one), that code reports a length of 2, meaning 2 "foreign" characters. I tried out without the LEN() to see exactly what those are and got - nothing.

Can anybody give me a hint as to what character can be there? Is a linebreak maybe counted as a character? -> If so, how can I include that in my Purgechar()_statement?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions

Re: "Foreign" text in a field

Hi DataNibbler,

len() should count every character und you could use ord() to return which character is be left. I'm not sure if it worked but you could create unvisible characters like a line-break in xls and use copy+paste to include these characters in your purgechar().

Maybe you need to extend your logic for a replace() or trim() part.

- Marcus

5 Replies
morganaaron
Valued Contributor

Re: "Foreign" text in a field

You could try adding: chr(13) into the PurgeChar statement to see if this is picking up any carriage returns? Outside of the single quotes, so try '/-0123' & chr(13) and see if they are the issue?

Re: "Foreign" text in a field

Hi DataNibbler,

len() should count every character und you could use ord() to return which character is be left. I'm not sure if it worked but you could create unvisible characters like a line-break in xls and use copy+paste to include these characters in your purgechar().

Maybe you need to extend your logic for a replace() or trim() part.

- Marcus

Re: "Foreign" text in a field

maybe chr(10) also

datanibbler
Esteemed Contributor

Re: "Foreign" text in a field

Hi,

I tried it out and when I include a TRIM() around the contents of the field,

- the "empty" line_breaks are gone and

- the "foreign" text also

=> so I guess it was the chr(10).

I will try out how it works out in the larger context of the entire code, but I guess that should do it.

Thanks a lot!

Best regards,

DataNibbler

datanibbler
Esteemed Contributor

Re: "Foreign" text in a field

Hi,

I have just found out that because I tested on just one set of possible base_lists and then on another, not "switching backward" so to speak, I have overlooked something:

- In that subroutine, I have two checks: One for linebreaks within a field and one for "foreign" text

- In a subsequent check, records with >=1 "foreign" character will be disregarded and an alarm will be triggered so 
   that someone will have a look and clean them manually.

<=> I just noticed that when there are linebreaks AND several lines actually filled in that one record, then there are also "foreign" characters, so it's probably the chr(10).

So I will now try out Aaron's suggestion with the chr(10).

Community Browser