topic Re: PurgeChar in Set Expression for Straight table in QlikView

PurgeChar in Set Expression for Straight table

patricesalem — Sun, 07 Jul 2019 06:43:03 GMT

Hello

I'm trying to get rid off some special characters loaded in the records of the "order_header" field (I don't have access to the data model, thus I can not load data already "purged".

I have tried several expressions, including this most simple one:

Count(distinct {<purgechar([ORDER_HEADER],'-') = {"*$(=(vPromo))*"},[D_SALES.autoCalendar.Week]={">=$(=vPromoStart-0)<=$(=vPromoEnd-0)"}>} ORDER_NB)

Nothing has worked so far....btw, if I remove the purgechar function, it works perfect...(so no issue with the rest of the count script)

I've been looking for hours for a solution...I would deeply appreciate if somebody could put me in the right way.

thanks in advance

Re: PurgeChar in Set Expression for Straight table

patricesalem — Sun, 07 Jul 2019 08:02:02 GMT

I'm advancing a bit but still stuck due to the "like" search

The below formula works great :

= Count(distinct {<[COMMANDE_CLIENT]= {"=purgechar(COMMANDE_CLIENT,' ')= '$(=vPromo)'"}>} COMMANDE_CLIENT)

nevertheless, I can't find a way to use * :

= Count(distinct {<[COMMANDE_CLIENT]= {"=purgechar(COMMANDE_CLIENT,' ')= '*$(=vPromo)*'"}>} COMMANDE_CLIENT)

nor

= Count(distinct {<[COMMANDE_CLIENT]= {"=purgechar(COMMANDE_CLIENT,' ')= *'$(=vPromo)'*"}>} COMMANDE_CLIENT)

any idea ?

thanks

Re: PurgeChar in Set Expression for Straight table

petter — Sun, 07 Jul 2019 08:17:33 GMT

Hi Patrice,

There is an operator called Like in Qlik expression language - so I am pretty sure you can do this:

= Count(Distinct {<[COMMANDE_CLIENT]= {"=PurgeChar(COMMANDE_CLIENT,' ') Like '*$(=vPromo)*' "}>} COMMANDE_CLIENT)

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Operators/StringOperators.htm

SOLVED: PurgeChar in Set Expression for Straight table

patricesalem — Sun, 07 Jul 2019 08:29:34 GMT

and the answer is

= Count(distinct {<
[COMMANDE_CLIENT]= {"=purgechar(COMMANDE_CLIENT,' _ -')like '*$(=(vPromo))*'"},
[D_SALES.autoCalendar.Week]={">=$(=vPromoStart-0)<=$(=vPromoEnd-0)"}
>} COMMANDE_CLIENT)

thanks to those who read my posts

Re: SOLVED: PurgeChar in Set Expression for Straight table

Albert_Candelario — Sun, 07 Jul 2019 08:41:57 GMT

Excellent!

Thanks for posting! Such a great Community 😊

Enjoy Qlik!