topic Re: And and Or in QlikView

And and Or

Anonymous — Thu, 07 May 2015 18:38:01 GMT

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

];

Re: And and Or

MarcoWedel — Thu, 07 May 2015 20:22:49 GMT

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

Re: And and Or

MarcoWedel — Thu, 07 May 2015 20:47:21 GMT

maybe like this:

hope this helps

regards

Marco

Re: And and Or

petter — Thu, 07 May 2015 20:48:03 GMT

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...

Re: And and Or

petter — Thu, 07 May 2015 20:55:34 GMT

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.

Re: And and Or

petter — Thu, 07 May 2015 21:00:12 GMT

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!

Re: And and Or

MarcoWedel — Thu, 07 May 2015 21:18:24 GMT

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

Re: And and Or

Anonymous — Thu, 07 May 2015 21:27:09 GMT

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?

Re: And and Or

petter — Thu, 07 May 2015 21:34:07 GMT

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 ....

Re: And and Or

petter — Thu, 07 May 2015 21:36:08 GMT

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...

Re: And and Or

Anonymous — Thu, 07 May 2015 21:36:30 GMT

Yes but the new properties has to appear on the screen for filtering as a listbox

Re: And and Or

Anonymous — Thu, 07 May 2015 21:46:08 GMT

Our initial approach was to keep the properties as individual columns. Generic load was one of our option to reload the properties dynamically, including a file with dynamic query (columns get added in the select) was another. But we end up at the same problem of showing the new properties as listbox selection or in a straight table. And thats when we decided to keep the properties as rows instead of columns. This solves the above problems but brings in the And/Or problem.

Re: And and Or

petter — Thu, 07 May 2015 21:54:09 GMT

Well QlikView does some magic for you in a lot of areas but I think you might be hoping for too much here ... Maybe not - I am out of suggestions for now - Good Luck anyway - I would love to see your solution some day if you care to share the concepts here on community

Re: And and Or

MarcoWedel — Thu, 07 May 2015 22:57:23 GMT

Hi,

another solution could be to select in a search object, that lists all fields/values:

hope this helps

regards

Marco

Re: And and Or

MarcoWedel — Thu, 07 May 2015 23:11:28 GMT

Hi,

yet another solution might be to use dynamic selection list boxes:

// test data generation

Prod:
LOAD 'P'&RecNo() as pid,
    Capitalize(KeepChar(Hash160(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')) as desc
AutoGenerate 20;

Properties:
LOAD *,
    pname&'='&pval as prop;
LOAD *,
    'Val_'&Capitalize(KeepChar(Hash128(pname&'/'&Ceil(Rand()*5)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'))  as pval;
LOAD 'P'&Ceil(Rand()*20) as pid,
    'Prop_'&Capitalize(KeepChar(Hash128(Ceil(Rand()*40)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')) as pname
AutoGenerate 1000;

// generic load

tabPropsGen:
Generic LOAD
  pid,
  pname,
  pval
Resident Properties;

tabProps:
LOAD Distinct pid Resident Properties;

DROP Table Properties;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabPropsGen.*') THEN
  LEFT Join ([tabProps]) LOAD * Resident [$(vTable)];
  DROP Table [$(vTable)];
  ENDIF
NEXT

hope this helps

regards

Marco

Re: And and Or

Anonymous — Fri, 08 May 2015 17:01:14 GMT

That looks promising. I will explore this and report. Thanks Marco.

Re: And and Or

Anonymous — Fri, 08 May 2015 18:16:23 GMT

Marco - This alternate solution works perfectly for listbox. Thanks.

I was trying to do same on a pivot (Property Value Table in attached qvw) or a straight table so the columns gets listed with values. Using the $field I could list the properties as columns but the values show up only if the field is selected in the Properties listbox. What am I missing?

Re: And and Or

Anonymous — Fri, 08 May 2015 20:23:59 GMT

Using the solution discussed here Chart with count of $Field values I could dynamically show the properties with values on a pivot table but only if the properties are less than 100. Apparently there is a limitation of 100 set on if loop depth.