And and Or

[Anonymous]

Helo,

We have data like below. The requirement is something like

In prop field if I select Color=Blue, Color=Brown, Length=5000mm, I should get pid=P2 and P3 but not P1. Basically the filter applied should be (Color = Blue OR Color = Brown) AND Length=5000mm.

I know the default is (Color = Blue OR Color = Brown OR Length=5000mm) and

in AND mode it is (Color = Blue AND Color = Brown AND Length=5000mm)

but is the combination (AND and OR) even possible?

We could keep Color and Length as separate columns but the property list is big and it keeps growing in every reload so that doesn't look like an option.

Prod:

LOAD * INLINE [

pid, desc

P1, Laptop

P2, Car

P3, PoolTable

];

Properties:

LOAD * Inline [

pid, pname, pval, prop

P1, Color, Blue, Color=Blue

P1, Width, 14inch, Width=14inch

P2, Color, Blue, Color=Blue

P2, HP, 500, HP=500

P2, Length, 5000mm, Length=5000mm

P3, Color, Brown, Color=Brown

P3, Length, 5000mm, Length=5000mm

];

[MarcoWedel]

Hi,

you could use seperate listboxes for the prop field in different alternate states and apply an appropriate set expression.

hope this helps

regards

Marco

[MarcoWedel]

maybe like this:

hope this helps

regards

Marco

[petter]

You should definitely look into GENERIC LOAD .... Exactly the type of functionality that will help you a lot for this type of associations.

QlikView Reference Manual.PDF  in chapter 27.1 Generic Databases page 391.

Basically you can put a GENERIC prefix in front of you load in the sample QVW you provided:

Properties:

GENERIC LOAD * Inline [

pid, pname, pval

P1, Color, Blue

P1, Width, 14inch

P2, Color, Blue

P2, HP, 500

P2, Length, 5000mm

P3, Color, Brown

P3, Length, 5000mm

];

And you can look at it with the table viewer CTRL+T

Now it has split each field into one table for each field including the key field which is pid.

That way every field in each table will be AND'ed with other fields in other tables and multiple selections within one field will be OR'ed.

So you can have exactly the requested logic you mentioned:

     (Color = Blue OR Color = Brown) AND Length=5000mm

Straight out of the box by adding just one single word - a single prefix to your LOAD statement - GENERIC !

This is quite amazing...

[petter]

Actually combining what Marco Wedel showed you with Generic might be very useful for you solution... Set Expressions used like that will give you tremendous flexibility in visualizing and querying your in-memory database.

[petter]

And a last thing to remember is that the tables that Generic Load creates for you are fullfilling the requirements for turning the ones you need into And-mode list boxes I think... I have tested that yet but I am pretty sure.

UPDATE:

I tested the And-mode compatibility of the tables. You will have to put DISTINCT as a keyword just after each LOAD:

     LOAD DISTINCT * INLINE [ ....

On both table loads and then you will be permitted to use AND on the List Boxes you want instead of OR logic between different values selected for the particular field. You will also in And-mode be allowed to do Forced-Exclusion.

Have a look at this community posting:

And-Mode: How to enable AND and NOT selections with Green, White, Grey...and Red!

[MarcoWedel]

maybe also interesting, if you like to recombine generic tables into one table:

rwunderlich‌ solution e.g. in Re: Regarding storing generic table into a qvd

regards

Marco

[Anonymous]

Thanks Marco and Petter.

These solution would work if there is a limited list of properties but we have like 100s of such properties, even worse they could get added every time we reload.

So is it possible, without any macro or custom, to dynamically create an alternate state or a list box whenever a new property gets added?

[petter]

I don't see why it is a problem with 100's of properties... and the way with Generic Load it will be created new ones dynamically ....

[petter]

When it comes to Macros they are no problem whatsoever as long as you use them for more administrative / application maintenance purposes... But letting many users or letting people accessing the server as such running Macros is seldom a good thing...