PurgeChar in Set Expression for Straight table

[patricesalem]

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

[patricesalem]

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

[petter · Accepted solution]

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/Opera...

[patricesalem]

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

[Albert_Candelario]

Excellent!

Thanks for posting! Such a great Community 😊

Enjoy Qlik!