Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Dpage
Contributor III
Contributor III

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
Master II
Master II

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

View solution in original post

5 Replies
trdandamudi
Master II
Master II

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
Contributor III
Contributor III
Author

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
Master II
Master II

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

View solution in original post

Dpage
Contributor III
Contributor III
Author

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
Master II
Master II

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.