topic Re: How to identify a record with an *? in QlikView

How to identify a record with an *?

datanibbler — Thu, 09 Oct 2014 06:15:53 GMT

Hi,

I need to look for records in an Excel_list containing the * - in those records, the providers just meant that the value of the field should be the same as in the record above, so there is just a *

<=> but the star is recognized by QlikView as a Wildcard, isn't it?

So how can I tell QlikView to look for records where there is the *?

Thanks a lot!

Best regards,

DataNibbler

Re: How to identify a record with an *?

anbu1984 — Thu, 09 Oct 2014 06:20:57 GMT

Like this

Load * where Index(Txt,'*');

Load * Inline [

Txt

hello

* ];

Re: How to identify a record with an *?

datanibbler — Thu, 09 Oct 2014 06:21:52 GMT

Hi,

I've got an idea - chr(42) should print the * - that way I could avoid actually using it.

Re: How to identify a record with an *?

anbu1984 — Thu, 09 Oct 2014 06:22:44 GMT

Load * where Index(Txt,'*') and Trim(Len(Txt))=1;

Load * Inline [

Txt

hello*

hello

* ];

EDIT:

Load * where Trim(Txt) ='*';

Load * Inline [

Txt

hello*

hello

* ];

Re: How to identify a record with an *?

jonathandienst — Thu, 09 Oct 2014 06:33:10 GMT

I would you use the transformation wizard (from Table Files... option from the script editor). Click Enable Transformation, and select the Fill tab. Click Fill..., enter the target column (the one to be filled), in the cell condition enter 'contains' and * and choose fill type 'Above'. This will add the filter expression (bold) to the file qualifier like this:

(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(Replace(1, top, StrCnd(contain, '*'))))

This will now autofill column 1 with the previous value when column 1 contains a *

HTH

Jonathan

Re: How to identify a record with an *?

datanibbler — Thu, 09 Oct 2014 06:40:29 GMT

Hi Jonathan,

that sounds really good and doable. I'd just rather try to do it "manually" in a separate step - in a RESIDENT LOAD. That way, my code will be easier to understand for others than if I do the transformation right in the primary LOAD.

Thanks a lot anyway!

I realized the chr(42) won't do, but I can use sth like Keepchar([field], '*').