Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Dpage
New Contributor

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

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?

Labels (1)
1 Solution

Accepted Solutions
trdandamudi
Honored Contributor

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

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])

5 Replies
trdandamudi
Honored Contributor

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

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
New Contributor

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

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
Honored Contributor

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

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
New Contributor

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

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
Honored Contributor

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

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.