table with some null values and show the total for them.

[#1]

Hello

I have 1 master table.

OPR_TYPE, OPR_NAME, RG_NAME, TOT_DUR

I have a config table that shows which are valid configrations.

OPR_TYPE,OPR_NAME, RG_NAME, VALID

i joined them to create 1 table

OPR_TYPE, OPR_NAME, RG_NAME, VALID, TOT_DUR

A,X,1,1,100

A,Y,2,-,200

B,LL,1,1,100

B,MM,1,-,300

- is where there is no entry for it in the config table, so the final table after join has that value as NULL.

if i create a pivot table that shows SUM({$<VALID ={"1"}> TOT_DUR) works fine.

but if i want to show totals of rows that do not have a config attached is not working:

i have the expression as SUM({$<VALID -={"1"}> TOT_DUR)

the table is always shown as null values. not with the ones that have actual totals but no VALID flag.

i have also tried with VALID ={} , VALID ={"$(=Null())"}  but to no avail.

is there something very basic i am missing?

[tresesco]

Have you tried like:

Sum(if(Isnull(VALID) or Len(Trim(VALID))=0, TOT_DUR)

?

[#3]

I think that the best solution here would be too use applymap instead of joining the master and the config table:

First create the mapping:

'Map_Config':

Mapping LOAD

     OPR_TYPE&'-'&OPR_NAME&'-'& RG_NAME as Id,

     VALID

From yourConfigTable;

and then use applymap in the master table:

Master_Table:

LOAD*,

     ApplyMap('Map_Config', OPR_TYPE&'-'&OPR_NAME&'-'& RG_NAME, 0) as Valid

From yourMasterTable;

This will bring the valid value if the row exists in the map, or a 0 value if it doesn't exist. Hence, you can later use set analysis with Valid={1} or Valid={0} to dismiss o select the valid values.

[Kushal_Chawda]

see the attached one..its working for me

[varunjain]

try and use Exclude

Indirect Set Analysis

• p( ) = Possible

• e( ) = Excluded

example--

sum({$<VALID= e({$<VALID={'1}>} TOT_DUR)

[#6]

HI,

You can get the answer with the expression

PFA it contains chart for valid=1 and valid=-,

[CELAMBARASAN]

Form a unique key combination if you dont have unique key field

Then try with

SUM({$<UKEY={"=IsNull(VALID)"}> TOT_DUR)

[#8]

Thank you guys.

i have tried all your solutions (except the one suggested by IgarriVM)  but somehow they are not working.

since i am joining the 2 tables. the VALID flag either is 1 or is null for rows that are in master table but not in the config table.

i am using QV10. if i make a simple table box with all the fields, even if i click on the VALID flag, it does not accept it as a current selection. is it something normal ? and causing this issue.

[varunjain]

try this

sum({$<VALID={'*'} - {'1'}>} TOT_DUR)

[#10]

Sorry that too isnt working.

i am attaching an example to help understand the scenario.

sorry cannot attach the original one.

but the problem is the same.