Skip to main content

Hi,

I need help fixing my PQL script


CASE
  WHEN "SHOPPING_DETAILS_DESC" LIKE '%CRIT% 1%' THEN 'Criteria 1'
  WHEN "SHOPPING_DETAILS_DESC" LIKE '%CRIT% 3%' THEN 'Criteria 3'
  WHEN "SHOPPING_DETAILS_DESC" LIKE '%CRIT% 5%' THEN 'Criteria 5'
  ELSE NULL
END

 

I am trying task search in a text field (Shopping_Details_Desc) for cases where ‘%CRIT%’ is identified. I would then like the code to assign a Criteria when the number ‘1’, ‘3’ or ‘5’ follow directly after the we wildcard search. I am pulling errors because there are other numbers in the text description and the code is incorrectly assigning them because it is find any any ‘1’ (as an example) that comes after ‘CRIT’ and then calling it a ‘Criteria 1’, even when it is a Criteria 3. 

 

My issue is that sometimes our operators are miss writing the text so we are trying to capture either CRIT or CRITERIA, or anything in between - which is why I want a wild card. 

 

I was able to write a statement in DAX to be able to look for a number assigned directly after the CRIT, but I cannot seem to figure out how to do this in PQL. 

Criteria Flag =
VAR CritType = eShopping_Details_Crit_Type]
RETURN
   IF(
     FIND("1", CritType, 1, 0) > 0,
     "Criteria 1",
      IF (
         FIND("3", CritType, 1, 0) > 0,
          "Criteria 3",
           IF (
             FIND("5", CritType, 1, 0) > 0,
               "Criteria 5",
                BLANK()
            )
        )



)

 

It was really lovely meeting many of you during the PI days last week and really appreciate anyone who can support resolving this!

 

Hello Christine, can you try this script and let me know if it works?

CASE WHEN MATCH_REGEX("SHOPPING_DETAILS_DESC", '.*CRIT(?:ERIA)?\s*1(?:\D|$).*') THEN 'Criteria 1' WHEN MATCH_REGEX("SHOPPING_DETAILS_DESC", '.*CRIT(?:ERIA)?\s*3(?:\D|$).*') THEN 'Criteria 3' WHEN MATCH_REGEX("SHOPPING_DETAILS_DESC", '.*CRIT(?:ERIA)?\s*5(?:\D|$).*') THEN 'Criteria 5' ELSE NULL END.

Hi Navya, 

 

Thank you for sharing this potential solution. I tried this script and am getting a formula error saying that ‘\s’ is an invalid character. I tried to flip the \ to / in case that was a mistype and got an error at line 26. 

Any ideas on how to fix this? 

 

Thanks for all your help!
 

 

Hey! ,

I’m so sorry that you have to face this error, can you try the following script and let me know?CASE WHEN MATCH_REGEX("SHOPPING_DETAILS_DESC", '.*CRIT(?:ERIA)?\s*1(1^0-9]|$).*') THEN 'Criteria 1' WHEN MATCH_REGEX("SHOPPING_DETAILS_DESC", '.*CRIT(?:ERIA)?\s*3(3^0-9]|$).*') THEN 'Criteria 3' WHEN MATCH_REGEX("SHOPPING_DETAILS_DESC", '.*CRIT(?:ERIA)?\s*5(5^0-9]|$).*') THEN 'Criteria 5' ELSE NULL END

Thank you for working to help resolve this, I am learning PQL from scratch so this is so helpful to figure out how to write a complex wild card function. I am still getting the same error with the ‘\s’ escape character. I have a case centric data model, idk if this is causing the issue? 

 

Thank you again for all your help!

BR

a

 

 

Reply


Community Code of Conduct