topic Writing an expression that encompasses data in 2 tables in QlikView

Writing an expression that encompasses data in 2 tables

Thu, 23 Feb 2012 09:38:22 GMT

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

Thu, 23 Feb 2012 11:19:08 GMT

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

Writing an expression that encompasses data in 2 tables

mdmukramali — Thu, 23 Feb 2012 12:27:24 GMT

Dear,

Paul.

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

Writing an expression that encompasses data in 2 tables

Thu, 23 Feb 2012 13:37:59 GMT

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

Writing an expression that encompasses data in 2 tables

Thu, 23 Feb 2012 14:26:40 GMT

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

CELAMBARASAN — Thu, 23 Feb 2012 14:32:30 GMT

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

Thu, 23 Feb 2012 14:49:59 GMT

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.

Writing an expression that encompasses data in 2 tables

CELAMBARASAN — Thu, 23 Feb 2012 15:03:55 GMT

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

Writing an expression that encompasses data in 2 tables

Thu, 23 Feb 2012 15:32:53 GMT

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.