Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Karl,
I added I listbox with =len(number2)
Why the 0 value is always grayed?
Thanks
Rocco
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.