topic Re: Limited list box values discrepancy in QlikView

Limited list box values discrepancy

Anonymous — Thu, 26 Nov 2015 16:46:41 GMT

Hi,

Im trying to build an ad-hoc report with dynamic dimensions. For this reason I created an excel file where QV loads the dimension information (about 50 dimensions).

dimensions:

LOAD _dimensionNo,

_dimensionLabel,

_dimension,

_group

FROM

[$(vExternalDataPath)dimensions.xlsx]

(ooxml, embedded labels, table is Sheet1);

I would like to keep the excel file because its easier to adjust than INLINE LOAD.

This worked totally fine with adding and removing the dimensions to the report from one big list-box. Now I wanted to group the different dimensions by information into multiple list-boxes which also worked totally fine with an expression instead of field:

=IF(match(_group, 'OriginInfo'),_dimensionLabel)

Now I got my dimensions all neat and tidy sorted into 6 list-box and here comes my dilemma.

I can select multiple values from one list-box and they show up correctly. In the statusbox I can see that by clicking one value, also the linked _group is selected.

When trying to add a value from another list-box / _group the current _group gets de-selected and also the related values I selected before.

Any ideas how to work around that?

I thought about creating tables for each _group but this would not work with my dynamic dimension selection in the report

I appreciate any input!!

- Lucas

Re: Limited list box values discrepancy

thomaslg_wq — Fri, 27 Nov 2015 09:55:42 GMT

Hi,

Why wouldn't it work with your dynamic dimension selection ?

Re: Limited list box values discrepancy

Anonymous — Fri, 27 Nov 2015 12:42:41 GMT

Because I would need to rename all the fields acoordingly to their group

_OriginDimensionNo,

_OriginDimensionLabel,

_OriginDimension,

_OriginGroup

I tried to just load the groups in seperate tables without renaming and QV just merged the tables to one because of the common field names.

And re-naming would not work with my dynamic dimensions because im refering to the field "DimensionLabel"

Re: Limited list box values discrepancy

thomaslg_wq — Fri, 27 Nov 2015 13:12:57 GMT

You could rename and then when referring, doing as :

'['&$(=

subfield(

if(len(getfieldseletion(_1stOriginDimensionNo)>0,concatenate(_1stOriginDimensionNo,';')

&if(len(getfieldseletion(_2ndOriginDimensionNo)>0,';'&concatenate(_2ndOriginDimensionNo,';')

&if(len(getfieldseletion(_3rdOriginDimensionNo)>0,';'&concatenate(_3rdOriginDimensionNo,';')

& go on

,';'

,1 (as your first dynamic dimension)

)

)&']'

Maybe you could send some scrambled application with fake data but real separate dynamic dimension tables by group?

Re: Limited list box values discrepancy

rubenmarin — Fri, 27 Nov 2015 13:26:38 GMT

Hi Lucas, maybe changing your selection method to check boxes works as expected. This is set on presentation tab:

Re: Limited list box values discrepancy

Anonymous — Fri, 27 Nov 2015 13:33:10 GMT

Thanks Ruben, Im already using the LED Checkboxes.

Thomas, I will reduce the datamodel and upload my qvw

Re: Limited list box values discrepancy

Anonymous — Fri, 27 Nov 2015 15:26:57 GMT

Thomas,

I don't really understand what your script is doing differently to mine. If you could explain the syntax a little I would really appreciate.

=$(=only({$ <_dimensionNo={1}>} _dimension))

I attached my QVW with one month data but it should be enough to explain the matter.

In the QVW I have my current "solution" on the one sheet explained and the problematic situation on the next one.

Re: Limited list box values discrepancy

thomaslg_wq — Fri, 27 Nov 2015 16:13:07 GMT

Lucas,

Here is your data model with renamed dimensions tables upon group.

I created only 3 dynamic dimensions from 2 possibles groups in your dynamic tab but you will be able to create as many as you wish by adding others "concat" and Number of subfield;

I explain the first dimension

=$(=subfield(

if(len(GetFieldSelections(_dimension_DestinationInfo))>0,concat(_dimension_DestinationInfo,';'),'')

&';'

&if(len(GetFieldSelections(_dimension_GoodsInfo))>0,concat(_dimension_GoodsInfo,';'),'')

,';',1))

Meaning:

Subfield function will search a string between 2 identical characters, at a desired place, the 2 parameters are ';' and '1' in this first dimension.

/////// following is a long concat of selected values, separated by a ";"

I concat every value of the field _dimension_DestinationInfo if there is at least one selection on it, separated by ";"

I separated by ";"

I concat every value of the field _dimension_GoodsInfo if there is at least one selection on it;seperated by ";"

You can add concat for others dimensions groups, and you'll have to change the number each time.

Hope it's clear...

Re: Limited list box values discrepancy

settu_periasamy — Fri, 27 Nov 2015 17:06:47 GMT

Hi Lucas,

I think it is because of the ListBox Expression.

=IF(match(_group, 'ShipmentInfo'),_dimensionLabel)

suppose, if you select the value in the 'ShipmentInfo' ListBox, It will filter the Shipment Info related values. Like the below screen shot.

You can select, Multiple Label from the 'Shipment Info' value. Again, if you selecting the Label from other 'Info' ListBox, It will override the values. see the below screen shot.. (I don't select the value in the _group ListBox,

Just i selected the value 'Destination info' ListBox. -> Because of the same ListBox)

As i know, May be the workaround is, create the Separate Island table for each Info.

Re: Limited list box values discrepancy

Anonymous — Mon, 30 Nov 2015 08:43:03 GMT

Hey Thomas,

quite a a way to work around. I will try to implement this into my report.

Whats the reasoning behind the Binary Load? Is it necessary?

Thank you for your effort!!

Lucas

Re: Limited list box values discrepancy

Anonymous — Mon, 30 Nov 2015 08:47:54 GMT

Hi Settu,

Thanks your input. That was my initial problem which I was trying to find a solution for.

I didnt know how to make different island tables work with my original formula for dynamic dimensions.

Thomas found a solution:

=$(=subfield(

if(len(GetFieldSelections(_dimension_DestinationInfo))>0,concat(_dimension_DestinationInfo,';'),'')

&';'

&if(len(GetFieldSelections(_dimension_GoodsInfo))>0,concat(_dimension_GoodsInfo,';'),'')

,';',1))

- Lucas

Re: Limited list box values discrepancy

thomaslg_wq — Mon, 30 Nov 2015 10:31:24 GMT

Hi Lucas,

I often use this trick

No it is not necessary, I did use it to keep your data and create several dimension tables.

Thomas