topic Re: List Box TreeView, Setting Expression in General Tab - Field in QlikView

List Box TreeView, Setting Expression in General Tab - Field

marleygt — Fri, 05 Jun 2015 09:30:50 GMT

Hi All!

I have some trouble with setting an Expression in a Listbox.

Assumption: I can't use Hierarchy/HierarchyBelongsTo.

Can I found a way to get all the fields selection I desire?

This is the situation:

Normally I have two specified Filter called "Category" (containing 'Livello2' field infos) and "SubCategory" (Filtering 'Livello3' field infos).

Everything is working properly as the two filters works separetely in their own ListBox.

Now the request is to create one single Filter in one single Listbox, with the two filters working together as a common TreeView.

The fields I need to manage are called 'Livello2' and 'Livello3'.

To let the Filter work I tryout this Exp:

=if(Livello3<>Null(),Livello2&'|'&Livello2&Livello3)

and flagged "show as TreeView" with '|' separator.

In this way I can properly see all the field correctly but, I cannot made the selections I need.

For instance, I can select any of Livello3, but not the Livello2.

I found a lot of solutions with Hierarchy/HierarchyBelongsTo but I can't use in this project.

The final question is: there is a way to make 'Livello2' selectable in this situation?

Thanks for attention and for any suggestions!

Alex

Re: List Box TreeView, Setting Expression in General Tab - Field

MarcoWedel — Fri, 05 Jun 2015 09:54:08 GMT

Hi,

you have to add seperate field values to a new field (e.g. "Livello2/3") containing the Livello2 values and the Livell2/3 combinations to be able to select the Livello2 groups.

Re: List Box TreeView, Setting Expression in General Tab - Field

marleygt — Mon, 08 Jun 2015 12:38:48 GMT

Hi Marco!

Thank you for your fast reply.

I've tried your suggestion but, maybe my fault, I am not able to reach the desired result.

Is possible that I am doing wrong the expression...could you please have a look to the attachment?

On the first row I put a similar situation as it is right now: "Main-Category-SubCategory" - "CatandSubcat" should be the new double filter which I am not able to select nothing about "livel1".

Then I've tried 3 instances with the LevelA (livel1+livel2) as you suggested, but still not able to select livel1 in the listbox.

What am I doing wrong?

Thank you!

Alex

Re: List Box TreeView, Setting Expression in General Tab - Field

MarcoWedel — Mon, 08 Jun 2015 22:08:56 GMT

Hi,

one solution could be:

table1:
LOAD *,
    AutoNumberHash128(Main,Category,Subcategory) as %SelectID,
    Main&'/'&Category&'/'& Subcategory as TempPath
INLINE [
    Main, Category, Subcategory
    Food, Pasta, Barilla
    Food, Pasta, Buitoni
    Food, Rice, Basmati
    Food, Rice, Classic
    Food, Meat, Filet
    Food, Meat, Rib
    Food, Fish, salmon
    Food, Fish, SwordFish
    Drinks, Wine, Red
    Drinks, Wine, White
    Drinks, Beer, Strong
    Drinks, Beer, Lite
];

tabSelectionPath:
LOAD Distinct
    %SelectID,
    Left(TempPath,Index(TempPath&'/','/',IterNo())-1) as SelectionPath 
Resident table1 
While IterNo()<=SubStringCount(TempPath,'/')+1;

hope this helps

regards

Marco

Re: List Box TreeView, Setting Expression in General Tab - Field

marleygt — Tue, 09 Jun 2015 12:54:40 GMT

YES!

It works!

Thank you Marco! Amazing!

I feel satisfied about the solution, and I've already implemented in my project.

However, I need to ask you another thing, in this case not so critical, but if we can find a way, should be great and the final product could be considered 100% top functional.

The problem is this: not all the Level are fully completed; some are with Level 2 full and other have less option, or the Level 3 not existing.

To use our previous example, as we have another "Category" stuff, such as Biscuit or - I don't know - Candies, but no level 3 at all, for instance, or just one of possible alternative; or to be more clear, as I have to put a new "Category" of wine - like "spanish wine", but in Subcategory, only in this case, just red Spanish Wine.

What is happens to me right now, is that where there are no choice available, remain a white blank space, as there might be a checkbox but is not visible at all.

This make the final presentation of the filter, not fitting good, due to the fact that Level 2 and 3 in my project are in certain case, dense of fields.

The question is: is possible to avoid blank spaces and let the filter appears exactly as the SelectionPath you design?

In anycase, thank you again for helping me!

Have a nice day!

Alex

Re: List Box TreeView, Setting Expression in General Tab - Field

marleygt — Thu, 25 Jun 2015 12:56:56 GMT

Hi MarcoWedel

I need a last help for this situation.

In the attach you'll find your last listbox modified with 2 more data.

If you pay attention, you will notice a couple of data with no info on the "3rd Level" (SubCategory), and this means they generate a blank subspace.

The request is: is possible to remove the blank spaces under "Water" and "Pastina"?

Thank you in advance for any ideas or resolution!

Alex

Re: List Box TreeView, Setting Expression in General Tab - Field

sunny_talwar — Mon, 29 Jun 2015 10:46:56 GMT

You can try it in couple of ways:

Using the expression:

=If(Len(Trim(SubField(SelectionPath, '/', -1))) > 0, SelectionPath)

or in the script:

table1:

LOAD *,

AutoNumberHash128(Main,Category,Subcategory) as %SelectID,

Main&'/'&Category & If(Len(Trim(Subcategory)) > 0, '/' & Subcategory) as TempPath

INLINE [

Main, Category, Subcategory

Food, Pasta, Barilla

Food, Pasta, Buitoni

Food, Pastina,

Food, Rice, Basmati

Food, Rice, Classic

Food, Meat, Filet

Food, Meat, Rib

Food, Fish, salmon

Food, Fish, SwordFish

Drinks, Wine, Red

Drinks, Wine, White

Drinks, Beer, Strong

Drinks, Beer, Lite

Drinks, Water,

];

tabSelectionPath:

LOAD Distinct

%SelectID,

Left(TempPath,Index(TempPath&'/','/',IterNo())-1) as SelectionPath

Resident table1

While IterNo()<=SubStringCount(TempPath,'/')+1;

The only problem is, it gives you a weird looking output where Water and Pastina are somewhat indented from there original spot.

HTH

Best,

Sunny

Re: List Box TreeView, Setting Expression in General Tab - Field

marleygt — Mon, 29 Jun 2015 11:01:24 GMT

WOW!

I think it's ok in this way.

I am going to implement in the original project; the treeview will be more huge and with a lots of leafs, so now it depends from the global view, how it looks and if the customers don't complain about that, but for me it's enough from the moment it's correctly working, and without any blank spaces.

Thank you!

Re: List Box TreeView, Setting Expression in General Tab - Field

sunny_talwar — Mon, 29 Jun 2015 11:05:29 GMT

Awesome

I am glad we were able to help. When I say 'we', I mean MarcoWedel‌‌ and me because I just used his output to make a small tweak to get you what you wanted. So most of the credit goes to him (I can take a small credit )

Have a good one.

Best,

Sunny