Can I make combined selections from two fields?

[Anonymous]

Hi there,

Please see attached QVW file.

As a user, I would like to be able to make a selection of two fields...

I have two different fields for "Region", one from invoice header and one from invoice line.

I would like to make a selection that includes all rows with a specific value from the two fields. Is that possible?

Thank you for advice!

Script follows (but see QVW for full details...)

InvoiceHead:

Load * Inline [

    VoucherId, Head_CostCenterId

    A, X

    B, Z

    C, Z

    D, X

    E, Y

];

InvoiceLine:

Load * Inline [

    VoucherId, VoucherLineNo, Line_CostCenterId

    A, 1, X

    A, 2, X

    B, 1, Z

    C, 1, Z

    D, 1, Z

    D, 2, Z

    D, 3, Y

    E, 1, Y

];

CostCenter_Line:

Load * Inline [

    Line_CostCenterId, Line_Region

    X, North

    Y, Mid

    Z, South

];

CostCenter_Head:

Load * Inline [

    Head_CostCenterId, Head_Region

    X, North

    Y, Mid

    Z, South

];

[Anonymous]

Hi

Are you going to any calculations after selection ?

[Anonymous]

No, I just want to make a selection... It will be used for reducing data in QV Publisher.. For instance, I want to publish a document to a sales manager i region North, with all data from North (regardless head och line).

[swuehl]

If you manage to bring both region fields into one resident table (like creating a single fact table by joining all your tables), you can follow

Canonical Date

and create a canonical region field you can make your selection in.

[Anil_Babu_Samineni]

What is expected model?

[Anonymous]

Thanks. I understand.

But as a user, it's not possible to make selections (filter data) over multiple fields like this?

In SQL you can just write an OR statement, which in this case would be:

SELECT

     ...

WHERE Head_Region='North' OR Line_Region='North'

[marcus_sommer]

I have no experience to the publisher-reducing but maybe the following is as idea helpful:

- combining both region-fields like: Head_Region & '|' & Line_Region as Region

- where clause logic of: where wildmatch(Region, '*North*')

- Marcus

[swuehl]

Thanks. I understand.

But as a user, it's not possible to make selections (filter data) over multiple fields like this?

Not easily.

You can use set analysis with a union of sets:

=Count({<Line_Region = {North}>+<Head_Region = {North}>} VoucherId & VoucherLineNo)

returns 5, like in your above sample.