Skip to main content
Veteran
Apr 21, 2023 8:08 PM UTC

Can I use a middle digit to sort/filter?

Hello! 

My brain is mush so I'm posting this for the bigger audience to please help me brainstorm or answer me directly if it's possible 🙂 

Can I use a middle digit on an account name in a member filter? For example, the 4th digit of our cost centers represents location but I'm wondering if I can actually do anything with that. I want to list all CCs with a 4 in that spot but can't quite figure out a good member filter that works. I tried ? like the placeholders in transformation rules and * to no avail. Here's a sample member filter for reference (I'm using it in a QV):

A#RD_tot.base.where(name starts with 'CC_SAP_***4')

Any thoughts? I could add that fourth digit in a text field or otherwise create an alternate hierarchy but I'm wondering if there's a way to utilize it as is. Any thoughts are welcome! 

Thanks & happy Friday! 

8 replies

Member
Apr 21, 2023 10:22 PM UTC

Hi Nicole! Can you be more specific about where you're doing this member filter?  Is it in a member formula? Or in a Cube View or Dashboard?  Thanks.

~ Paula 

(PS Congrats on the award at Splash!)

Veteran
Apr 24, 2023 11:58 AM UTC

Thanks for the kind words! I'm using it in a quick view. 

Member
Apr 22, 2023 12:54 AM UTC

Hmmm, I don't think wild card works in member filter. I guess you could create a business rule and ask the use to pass the mid number and get those members which has the 4. I guess that will be the least effort one. Else like you said you can add that as a text attribute and do it that way as well.

ChristianW
Employee - Advisor
Employee - Advisor
Apr 22, 2023 4:44 PM UTC

Hi Nicole

As mentioned by ckattokaran, you can use a custom (business rule based) list for this purpose, here you can use Linq queries, string operations, regular expressions and more to solve your problem. Custom lists can be used with QVs.

But, I do not believe, that this kind of queries against label names are a good solution. I prefer to use the text arguments for this purpose.

Cheers

Christian

Veteran
Apr 24, 2023 6:18 AM UTC

Hi Nicole,

You can set a specific Text attribute say Text2 for all the CCs with a 4 in the required spot "XYZ" and use that in your member filter.

A#RD_tot.base.where(Text2 = XYZ) 

This will be a maintenance task if your CCs change, but picking these using Text attribute would be easier.

Hope this helps.

 

Employee - Sage
Apr 24, 2023 8:36 AM UTC

If your fourth digit is the last and you always have 4 digits, you can combine Where clauses: A#RD_tot.Base.Where((Name StartsWith 'CC_SAP_') and (Name EndsWith 4))

If any of those conditions do not apply, though (i.e. more digits can appear after the fourth, and/or there is a variable number of digits), then you need Text properties (no coding required) or a Custom Member List or XFBR (coding required).

Veteran
Apr 24, 2023 12:00 PM UTC

Hello! 

The CCs are 10 digits long, after the CC_SAP_, so the 4th digit is in the middle. Thanks though! 

Veteran
Apr 24, 2023 12:02 PM UTC

Thanks all - I figured it'd be more work than just a member filter but was hoping I was wrong!! Appreciate all the responses!