topic Re: Script Wildcard not working as expected in QlikView

Script Wildcard not working as expected

Wed, 25 Nov 2020 16:16:04 GMT

I am trying to eliminate unwanted junk data from my list box views so I added the command to the script. It is not working so I'm asking for some feedback on what is the correct way to do this.

Here is the junk I'm trying to eliminate:

And here are the commands:

FROM "DW_LMKPSFT"."PSD_PS_XR_COMPANY"
WHERE BO_NAME <> 'ABCDE Guest' or BO_NAME <>'*OUT*' or BO_NAME <> '*DUP*';

Also:

SQL SELECT "CASE_CONTACT",
NAME as CONTACT_NAME
FROM "DW_LMKPSFT"."PS_XR_CONT_NAME_VW"
WHERE NAME <>'*??**' or NAME <>'*zzz**'
;

Any help is greatly appreciated!

Re: Script Wildcard not working as expected

sunny_talwar — Wed, 12 Oct 2016 16:16:25 GMT

I think since you are using your where statement in SQL, you need to use SQL syntax here (SQL Wildcards). May be try this:

SQL SELECT "CASE_CONTACT",
NAME as CONTACT_NAME
FROM "DW_LMKPSFT"."PS_XR_CONT_NAME_VW"
WHERE NAME NOT LIKE '??%' or NAME NOT LIKE 'zzz%'
;

Re: Script Wildcard not working as expected

Anil_Babu_Samineni — Wed, 12 Oct 2016 16:18:09 GMT

You can use PurgeChar(FieldName, '!@#$%^&*()_+') as FieldName

Re: Script Wildcard not working as expected

Wed, 12 Oct 2016 17:14:53 GMT

This works for just the first item, so the "or" does not work for the next item. Any suggestions?

Re: Script Wildcard not working as expected

sunny_talwar — Wed, 12 Oct 2016 17:19:52 GMT

That is strange, i would have thought that this works. Can you test the sql outside of QlikView to see if it still doesn't work? Like in TOAD or whatever you use to run queries.

Re: Script Wildcard not working as expected

Wed, 12 Oct 2016 18:32:05 GMT

I got it...thanks for your help!

Re: Script Wildcard not working as expected

sunny_talwar — Wed, 12 Oct 2016 18:33:14 GMT

Would you be able to point out what exactly worked for you? May be this might help someone else in the future also.

Thanks,

Sunny

Re: Script Wildcard not working as expected

Wed, 12 Oct 2016 18:38:03 GMT

Sure...I used 'and' instead of 'or' and I removed the % sign from the beginning of the string (if there were no preceding characters ahead of the text I wanted to remove)

Re: Script Wildcard not working as expected

sunny_talwar — Wed, 12 Oct 2016 18:40:08 GMT

Ya, I did remove them from the start when I gave you, but did not think you would need and instead of or. But that is the sort of thing that always confuses me.

Anyways, I am glad it finally worked.

Best,

Sunny

Re: Script Wildcard not working as expected

Wed, 12 Oct 2016 18:40:58 GMT

here is the command I ended up with that provides the desired data:

WHERE NAME NOT LIKE 'zz%' and NAME NOT LIKE '??%' and NAME NOT LIKE '(%'

and

WHERE BO_NAME NOT LIKE '%*%' and BO_NAME NOT LIKE 'ABCDE%'

Re: Script Wildcard not working as expected

sunny_talwar — Wed, 12 Oct 2016 18:45:22 GMT

Thanks for sharing this John!!