I want to exclude Item Codes, from the dimension, with specific suffixes and prefixes

[Dpage]

Hello,

I have been trying to create a Pivot table that shows me my inventory items, the preferred vendor, my Quantity on Hand, the associated Reorder or Build Points(par Levels), and the Quarterly Avg. Usage from the last 12 months.

The dimensions are Item.Full Name, Item.Sales Description, and Item.Preferred Vendor. I would like to exclude the Item Codes from the Item.Full Name dimension that end in BIP and begin in SP. I can exclude something specific but can't seem to make it work based on the Prefix and Suffix. I've tried using 

=If(not Match([Item.Full Name],'*BIP'),[Item.Full Name])

In set analysis the * seemed to work as a wildcard and would include everything ending in BIP. Doesn't seem to work for an If statement. Any suggestions?

[trdandamudi]

One way you can do is as below:

=If( not Mid([Item.Full Name],1,2)='SP' AND not Mid([Item.Full Name],Len(Name)-2,3)='BIP',[Item.Full Name])

Hope this helps.

[Dpage]

Hi,

Thanks for the response. That appeared to work for the Prefix 'SP' but not for the Suffix 'BIP'. The section Len(Name) doesn't appear to be doing anything. Says Name is a bad field name.

I should note, I am working in Quickbooks Advanced reporting which is powered by Qlikview Software and I have been able to solve many problems by checking these forums as everything I've come across appears to work. That said, not sure if the Len(Name) aspect doesn't jive with the program I am in.

Thanks again

[trdandamudi · Accepted solution]

My bad it is a typo error: please use the below:

=If( not Mid([Item.Full Name],1,2)='SP' AND not Mid([Item.Full Name],Len([Item.Full Name])-2,3)='BIP',[Item.Full Name])

[Dpage]

Thank you very much. I should have realized and tried to use the actual field name. My bad too. Now I will have to research why it worked.

Thanks!

[trdandamudi]

You are welcome and happy to help. Please look into the functions Mid() and Len() in the help and you will understand how it is working.