topic Re: Set analysis - with 2 fields in QlikView

Set analysis - with 2 fields

dafnis14 — Wed, 28 May 2014 10:39:04 GMT

Hi,

I have a chart with a dimension SupplierCode.

i would like to use the dimension in order to select values linked to the current supplier

in another table.

The following does not work:

sum({SupplierCode = P(Suppl.SuppCode>}Suppl.Quantity*Suppl.UnitPrice)

would appreciate help with this issue!

Thanks!

Re: Set analysis - with 2 fields

jvitantonio — Wed, 28 May 2014 10:43:27 GMT

Try this:

sum({< SupplierCode = P(Suppl.SuppCode) >} Suppl.Quantity*Suppl.UnitPrice)

Thanks

Re: Set analysis - with 2 fields

PradeepReddy — Wed, 28 May 2014 10:45:11 GMT

try these

sum({<SupplierCode ={'*'}>}Suppl.Quantity*Suppl.UnitPrice)

Re: Set analysis - with 2 fields

Wed, 28 May 2014 10:45:12 GMT

I don't really understand what you are trying to do. becaseu as soon as you select a supplier code the other table should show the right values.

regards,

Re: Set analysis - with 2 fields

dafnis14 — Wed, 28 May 2014 15:56:32 GMT

Thanks.. but it didn't help.. 😞

Re: Set analysis - with 2 fields

dafnis14 — Wed, 28 May 2014 16:03:28 GMT

Hi,

There is no link between the tables.

Let's assume you have 2 tables with suppliers facts with no common supplier code key:

Suppliers and Suppl. The supplier code has different field name in the tables.

The SupplierCode from Suppliers table is a dimension in a chart.

Not all the suppliers that are in the chart have values in Suppl table.

But for the ones that do have, I want to be able to show the following :

sum({SupplierCode = P(Suppl.SuppCode)>}Suppl.Quantity*Suppl.UnitPrice)

Thanks!

Re: Set analysis - with 2 fields

PradeepReddy — Wed, 28 May 2014 18:57:03 GMT

Hi,

Can you please provide some sample Application or Data... So that it will be easy to analyse the scenario.

Thanks,

Pradeep

Re: Set analysis - with 2 fields

jvitantonio — Thu, 29 May 2014 08:29:53 GMT

Hello Dafnis,

You are missing "<" after your curly bracket.

It should be like the following:

sum({<SupplierCode = P(Suppl.SuppCode)>}Suppl.Quantity*Suppl.UnitPrice)

Thanks,

Re: Set analysis - with 2 fields

jonathandienst — Thu, 29 May 2014 08:44:30 GMT

First of all, you cannot use a dimension value in a set expression. The set expression is evaluated once for the chart and not once per dimension value.

Secondly, you need to get information from the Suppl table. A selection of Supplier does not help as this is not linked to Suppl. You need something like this:

Sum(If(Suppl.SuppCode = SupplierCode, Suppl.Quantity * Suppl.UnitPrice))

If that performs poorly (sum(if()) is a performance killer especially across two tables), you need to look more closely at your data model. The most common design is a star schema, where the supplier dimension should be linked to the fact table by the supplier code. You can do this when loading the suppliers by aliasing the Suppl.SuppCode to SupplierCode. Something like:

LOAD

....

Suppl.SuppCode, // use this field to see which suppliers exist in the dimension

Suppl.SuppCode As SuplierCode, // this field links to the fact table

...

FROM Suppliers....

Then in a table that has SupplierCode as a dimension, just use

Sum(Suppl.Quantity * Suppl.UnitPrice)

Perhaps you could post a reduced data sample of your qvw here.

HTH

Jonathan

Re: Set analysis - with 2 fields

dafnis14 — Fri, 30 May 2014 08:03:00 GMT

Hi Jonathan,

That's what I've done.

Thanks for this helpful advise!