topic Re: Derived Measure (fact) in QlikView

Derived Measure (fact)

Anonymous — Sat, 05 Dec 2015 21:42:36 GMT

I have the following in my load Script:

RangeSum(MedicalAidAmount, Shortfall) as Amount,

Is it possible to have a derived measure (fact) based on Amount, Like

0.2*Amount as OtherAmount

Regards.

Chris

Re: Derived Measure (fact)

sunny_talwar — Sat, 05 Dec 2015 21:45:02 GMT

Sure, try this:

LOAD RangeSum(MedicalAidAmount, Shortfall) as Amount,

RangeSum(MedicalAidAmount, Shortfall) * 0.2 as OtherAmount

FROM ...

LOAD Amount * 0.2 as OtherAmount;

LOAD RangeSum(MedicalAidAmount, Shortfall) as Amount

FROM ...

Re: Derived Measure (fact)

Anonymous — Sat, 05 Dec 2015 22:08:59 GMT

Thanks Sunny, is it possible to assign such a derived fact to a named dimension say Company1 so that it is associated with this value?

Regards.

Re: Derived Measure (fact)

sunny_talwar — Sat, 05 Dec 2015 22:12:04 GMT

You mean that OtherAmount should have 0.2*Amount for Company1 and all other companies should have 0s for OtherAmount field?

Re: Derived Measure (fact)

swuehl — Sat, 05 Dec 2015 22:39:42 GMT

Maybe like

LOAD RangeSum(MedicalAidAmount, Shortfall) * If(NamedDimension = 'Company1', 0.2,1) as Amount,

NamedDimension,

...

Re: Derived Measure (fact)

Anonymous — Sat, 05 Dec 2015 22:50:00 GMT

Thanks very much, i just want the new values to be associated with a named dimension. The original values are already assigned to different companies and branches as follows:

Company Branch QTY Amount

River SSS 45 56

Brigde XXX 50 400

Sand YYY 400 500

Manfred Fred OtherQTY OtherAmount

I now need OtherAmount and OtherQTY to be assigned to a DerivedDimension say Manfred and Fred as in bold above

Re: Derived Measure (fact)

sunny_talwar — Sat, 05 Dec 2015 22:58:47 GMT

How is OtherQTY and OtherAmount getting calculated here? From the data for River, Bridge and Sand? I am not really sure I understand what you are trying to do here.

Re: Derived Measure (fact)

Anonymous — Sat, 05 Dec 2015 23:08:19 GMT

Yes it is being calculated fro the River, Bridge and Sand Data. The OtherQTY and OtherAmount represents organisations which have not been sample and is an estimate for these organizations as a whole.

Re: Derived Measure (fact)

sunny_talwar — Sat, 05 Dec 2015 23:14:29 GMT

May be something like this:

Table:

LOAD Company,

Branch,

QTY,

Amount

FROM Source;

Concatenate (Table)

LOAD 'Manfred' as Company,

'Fred' as Branch,

Avg(QTY) * 0.2 as QTY,

Avg(Amount) * 0.2 as Amount

Resident Table;

For you sample, you will get a table like this:

Company Branch QTY Amount

River SSS 45 56

Brigde XXX 50 400

Sand YYY 400 500

Manfred Fred 39.67 63.73

Where QTY = Avg(45, 50, 400) * 0.2 and

Amount = Avg(56, 400, 500) * 0.2

Is this what you are looking for?

Re: Derived Measure (fact)

Anonymous — Sat, 05 Dec 2015 23:18:58 GMT

Hi Sunny, that should do, let me apply it and let you know.

Regards.

Re: Derived Measure (fact)

sunny_talwar — Sat, 05 Dec 2015 23:22:53 GMT

Ya try it out. With the information provided, I don't think we can offer more help. If it doesn't work please provide more sample so that we can offer a better solution

Best,

Sunny

Re: Derived Measure (fact)

Anonymous — Sat, 05 Dec 2015 23:54:51 GMT

Hi Sunny

I get an error message on Concatenate (Table)

the message says Table not found

Re: Derived Measure (fact)

sunny_talwar — Sat, 05 Dec 2015 23:57:21 GMT

What is your table name where you are concatenating it into?

Table:

LOAD Company,

Branch,

QTY,

Amount

FROM Source;

Concatenate (Table)

LOAD 'Manfred' as Company,

'Fred' as Branch,

Avg(QTY) * 0.2 as QTY,

Avg(Amount) * 0.2 as Amount

Resident Table;

In my example Concatenate (Table) matches the Table I am concatenating into.

HTH

Re: Derived Measure (fact)

Anonymous — Sun, 06 Dec 2015 00:12:24 GMT

Yes i have the that, here with error message:

I have the table [Main Data] in my model

Re: Derived Measure (fact)

sunny_talwar — Sun, 06 Dec 2015 00:15:11 GMT

Can you share your script?

Re: Derived Measure (fact)

Anonymous — Sun, 06 Dec 2015 00:19:00 GMT

Hi Sunny, managed to sort it out, script was in the wrong place. Will advise you of outcome

Re: Derived Measure (fact)

sunny_talwar — Sun, 06 Dec 2015 00:20:27 GMT

Sounds good

Re: Derived Measure (fact)

Anonymous — Sun, 06 Dec 2015 00:45:30 GMT

Thank you very much

Re: Derived Measure (fact)

Anonymous — Sun, 06 Dec 2015 01:03:12 GMT

Thanks Swuehl