8 Replies Latest reply: Feb 23, 2012 10:32 AM by mrpjspencer

# Writing an expression that encompasses data in 2 tables

I am trying to formulate an expression that encompasses data from two tables:-

Table1:

Amount,

Table2:

if(wildmatch(Rate_Category, 'A*'), 'Unmeasured',

if(wildmatch(Rate_Category, 'U*'), 'Unmeasured')) as Unmeasured,

if(wildmatch(Rate_Category, 'M*'), 'Measured') as Measured,

I am trying to get an amount for Unmeasured and Measured in seperate fields.

Paul.

• ###### Writing an expression that encompasses data in 2 tables

Anyone? I'm sat here bashing my head off a desk

• ###### Writing an expression that encompasses data in 2 tables

Dear,

Paul.

can you explain what do u want , some what clearly?

• ###### Writing an expression that encompasses data in 2 tables

try with:

if(left(Rate,1)='A' or left(Rate,1)='U','Unmeasured',

if(left(Rate,1)='M','Measured')) as Type

• ###### Writing an expression that encompasses data in 2 tables

That did give me some pointers, ive gone away from trying to entirely calculating it all in an expression.

Ive tried this which I cant get it to work.

if(left(Rate_Category,1)='A' or left(Rate_Category,1)='U', sum(Amount)) as unmeasured_Total,

if(left(Rate_Category,1)='M',Sum(Amount)) as Measured_Total,

What im trying to do is for the 1st line where the rate category starts with A or U to be counted by (Amount) as unmeasured_total

For the 2nd line  where Rate Category begins with M to be be counted as Measured_Total. Qlickview rejects this which ever way round I try it. Please help.

• ###### Writing an expression that encompasses data in 2 tables

Hi,

You may look in to this

Sum({<Rate_Category={'A*','U*'}>} Amount) for unmeasured_Total

Sum({<Rate_Category={'M*'}>} Amount) for Measured_Total

Hope it helps

Celambarasan

• ###### Writing an expression that encompasses data in 2 tables

doesnt work, probably I forgot to mention that I have this script before this in the same table.

if(wildmatch(Rate_Category, 'A*'), 'Unmeasured',

if(wildmatch(Rate_Category, 'U*'), 'Unmeasured')) as Unmeasured,

if(wildmatch(Rate_Category, 'M*'), 'Measured') as Measured,

Sum({<Rate_Category={'A*','U*'}>} Amount) for unmeasured_Total

Sum({<Rate_Category={'M*'}>} Amount) for Measured_Total

Paul.

• ###### Writing an expression that encompasses data in 2 tables

Hi,

Table1:

Rate_Category,

Amount

From..

Left Join(Table1)

Rate_Category,

if(wildmatch(Rate_Category, 'A*'), 'Unmeasured',

if(wildmatch(Rate_Category, 'U*'), 'Unmeasured')) as Unmeasured,

if(wildmatch(Rate_Category, 'M*'), 'Measured') as Measured

From..

UnMeasured:

Rate_Category,

Amount as unmeasure_Total

Resident Table1 where Unmeasured<>null();

Measured:

RateCategory,

Measure_Total

Resident

Table1 where Measured <> null();

Celambarasan

• ###### Writing an expression that encompasses data in 2 tables

Struggling to get that to work, even when i filled in the table names, surely there must be a much simpler way to do it

if(wildmatch(Rate_Category, 'A*'), 'Unmeasured',

if(wildmatch(Rate_Category, 'U*'), 'Unmeasured')) as Unmeasured,

if(wildmatch(Rate_Category, 'M*'), 'Measured') as Measured

Unmeasured and measured is a number of customers. I just want to get the value these 2 groups owe in  (£) as the field 'Amount'.

Im looking at building this in the same table (sorry being simple as i'm new to qlickview)

If customer is unmeasured please give me the amount represented by this value.