Writing an expression that encompasses data in 2 tables

[#1]

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.

[#2]

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

[mdmukramali]

Dear,

Paul.

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

[#4]

try with:

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

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

Hope this can help you

[#5]

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.

[CELAMBARASAN]

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

[#7]

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

isnt working, also my company is running version 9 of Qlickview, please help!

Paul.

[CELAMBARASAN]

Hi,

Table1:

Load

Rate_Category,

Amount

From..

Left Join(Table1)

Load

     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:

Load

     Rate_Category,

     Amount as unmeasure_Total

Resident Table1 where Unmeasured<>null();

Measured:

Load

     RateCategory,

     Measure_Total

Resident

     Table1 where Measured <> null();

Celambarasan

[#9]

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.