Filter

[#1]

Hi,

I have two tables:

table1 (key1, number1)

table2 (key2, key1, number2)

I need a pivot table with two dimensions key1 and key2 and three expressions: SUM(number1), number2 and SUM(number1)-SUM(number2).

How can I select:

- rows that have a number1-number2>10;

- rows into table1 that is not present in table2.

I don't want to use a JOIN function.

all_tmp_table:

load

key1 as key1_tmp

number1 as number1_tmp

resident table1;

join

load

key1 as key1_tmp,

key2 as key2_tmp,

number2 as number2_tmp

resident table2;

table:

load

key1_tmp as key_1

key2_tmp as key_2

if (number1-number2>10;1;0) as diff

if (isnull(number2);1;0) as yes_table1_no_table2

resident all_tmp_table;

Thanks

Rocco

[pover]

You could create a list box of key1 and try the following dynamic searches.

"=sum(number1-number2)>10"

or

"=len(number2)=0"

Or put these searches in the expression using set analysis.

sum({$<key1={"=sum(number1-number2)>10"}>} number1)

Regards.

[#3]

Hi Karl,

I added I listbox with =len(number2)

Why the 0 value is always grayed?

Thanks

Rocco

[pover]

You should create a listbox with key1 and do a search with =len(number2).

Based on what you've done the 0 value is gray in the =len(number2) listbox because the value null or empty has length 0 so that tells you that those values are being filtered out.

Regards.