Issue with filtering different parts of a main table.

[#1]

Hi,

I have a question regarding filtering different parts of a table.

I have a table that contains all the sales data I need.

Now, the customer stated that there is some information that needs to be filtered out and here's where I'm having the issue. I want to know what the best approach is.

I haven't had a lot of experience with qlivkeiw but I'll try to explain as best as I can:

I have the SALES: table which contains all the information I need. (unit cost, margin, ext cost, ext margin, qty, company, ect)

Now, these are some filters that I need to implement.

1. Exculde all those that have the (customer number = A) and have items with (unit or price = 0)

2. Exclude all those with (item code = B) and have items with (unit or price = 0)

3. Exclude all those that have (Company Name = C and item code = D) and have items with (unit or price = 0)

I was trying to implement those filters in the main table but was't working.

I then thought to break the table into smaller tables just including those filters that I need. Ex.

1. Include all those FROM main SALES: table  that customer number <> A AND item code <> B and Company <> C

1. Include all those FROM main SALES: table that have the (customer number = A) and have items with (unit and price > 0)

2. Include all those FROM main SALES: table  with (item code = B) and have items with (unit and price > 0)

3. Include all those FROM main SALES: table that have (Company Name = C and item code = D) and have items with (unit and price > 0)

and then concatenate them...if I follow this apporach I would need the main SALES: table to act like a VIEW and not be loaded into memory.

Hopefully somone can understand what I'm trying to do. If you have any questions just let me know and I'll do my best to answer. Thanks!

[#2]

So you need a table like if (unit=0 or price=0) and (Customer Number = A or Item Code = B or ( Company Name = C and Item Code = D) ).

[#3]

Assuming you want to do this in the report rather than in the load script you could try using set analysis.  Here you can define the sets of data that you want to work with which can override or supplement any document level filters.

code would be something like the following

SUM({$<Customer Number  = {"<>A"},Item Code = {"<>B"},Company = {"<>C"} Sales)

there are many posts that deal with set analysis better than I can explain. Also look in the developer reference manual for set analysis

[#4]

Hey, Thanksfor the quick reply.

I need to EXCLUDE those with unit = 0 or price = 0...so in other way i need both price and cost > 0.

Another problemis the parenthesis.. I mean, once it hits the Customer Number = A,it will FILTER everything except the items with that customer number.

I need to include everything else in the SALES table.

Just filtering multiple specific information. (This is my issue).

So, for testing purposes all I'm trying to do is

GET ALL SALES data

EXCEPT those records with (customer number = 'A' having items whose unit or cost price = 0).

So this means I should still have some records with the customer numer = 'A' in the table. And the unit and cost margin are both > 0 since im filtering the = 0 out.

If I can get this filter to work I can figure out the rest....I THINK.

[#5]

Hey, thanks for the reply.

FYI: I am needing this in the script. I will need to use this filter in all charts and objects createrd.

Look at reply above to see if that helps in understanding my issue a little more.

Again thanks for the help.