topic Re: How to clean field in text file - load script in QlikView

How to clean field in text file - load script

Tue, 02 Jun 2015 19:35:46 GMT

Hi!

I have an issue where I on occasion get a data dump in a text file where some characters are properly exported from the DB2 database into a text file but when the record is loaded into QV it stops at this record.

Is there a way in qlik to do a "clean" function? (e.g. in Excel there is Clean() to remove "illegal characters". As I don't know exactly what kind of illegal characters that could be entered from a user POV until it is too late, so I am looking for a generic 80/20 rule function of some kind.

Any advice or pointers (no I do not have access to another ETL tool of some kind).

Thanks,

Erik

Re: How to clean field in text file - load script

PabloOrtiz — Wed, 03 Jun 2015 08:56:13 GMT

Hey, you can upload the file to see the problem?

Re: How to clean field in text file - load script

marcus_sommer — Wed, 03 Jun 2015 10:34:29 GMT

Maybe this character is an "eof" and you need to add "no eof" within your file-format statement.

- Marcus

Re: How to clean field in text file - load script

Thu, 04 Jun 2015 07:32:17 GMT

Hi!

Sorry to say the file is 2,3 GB so I have a hard time extracting this

latest case. The only thing we know is that the character(s) is different

and suspect they come from copy/paste from like a web page or similar

Problem is we don't know the cases so we are looking for something that

could potentially catch a bulk of these types of characters.

BR,

Erik

On Wed, Jun 3, 2015 at 10:56 AM, Pablo Ortiz <qcwebmaster@qlikview.com>

Re: How to clean field in text file - load script

MarcoWedel — Thu, 04 Jun 2015 08:21:19 GMT

Maybe changing the Codepage in the load statement helps ?

Regards

Marco

Re: How to clean field in text file - load script

Thu, 04 Jun 2015 08:26:55 GMT

Hi Erik,

if you don't know which record has a pb, you can set up ErrorMode = 0; and QV will not stop but generate an error you can trap and rely to a recno() function to knwo the record

if all records are incriminated, you may try to use purgechar() function to remove (your suspiscious) characters from a string

best regards

Chris

Re: How to clean field in text file - load script

Thu, 04 Jun 2015 10:12:30 GMT

Thanks Chris!

That sounds like a good suggestion because that is exactly what happens.

The load "exits" on the faulty record(so it is actually the last record

loaded) and continues with the rest of the script, i.e. load of other

tables.

On Thu, Jun 4, 2015 at 10:27 AM, christianj <qcwebmaster@qlikview.com>

Re: How to clean field in text file - load script

Thu, 04 Jun 2015 10:22:20 GMT

Hi Erik

Not sure to understand if you are happy with the help provided or if you need further assistance ?

Chris

Re: How to clean field in text file - load script

Thu, 04 Jun 2015 10:37:06 GMT

Happy! We will try this and add s step to resume load on the next row in the text file. Keep u posted

Thanks

4 jun 2015 kl. 12:22 skrev christianj <qcwebmaster@qlikview.com>:
How to clean field in text file - load script
reply from christianj in New to QlikView - View the full discussion
Hi Erik

Not sure to understand if you are happy with the help provided or if you need further assistance ?

Chris
Reply to this message by replying to this email, or go to the message on Qlik Community
Start a new discussion in New to QlikView by email or at Qlik Community
Following How to clean field in text file - load script in these streams: Inbox
© 1993-2014 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

Re: How to clean field in text file - load script

Thu, 15 Jun 2017 05:44:31 GMT

Hi Christian,

how exactly can you trap the error?

rgds

Dara