topic Re: Dynamically buidling columns in the straight table-duplicate columns in QlikView

Dynamically buidling columns in the straight table-duplicate columns

Fri, 14 Mar 2014 18:02:05 GMT

Hi All:

I need to build a straight table based on the user selection.

User can select/unselect measure/s from the list box (check box style) and based on the selections, columns will be added or removed from the straight table.

Based on previous posts on the community regarding this issue, i created an Inline table and then used it in the conditional section of the expression. My expression in the "conditional" tab looks

=getselectedcount ( _Measure )>0 and SubStringCount(Concat(_Measure, '|'), 'XLR')

this works fine if the value XLR is unique in the list box,

but if other values in the list box contains this word then it shows up 2 columns in the dashboard, even though user have selected one value.

i.e. if list box contains values like XLR, XLR Xigma, then my Straight table shows 2 columns(even though user selected just XLR Xigma) because XLR exist twice.

How can i build this table.

Please help.

Re: Dynamically buidling columns in the straight table-duplicate columns

Gysbert_Wassenaar — Fri, 14 Mar 2014 18:05:18 GMT

Change the substringcount part by adding an extra character that your dimension values very like won't have: SubStringCount(Concat(_Measure & '^' , '|'), 'XLR^')

Re: Dynamically buidling columns in the straight table-duplicate columns

nagaiank — Fri, 14 Mar 2014 18:08:17 GMT

I also had a similar problem. I solved it by defining a _DimensionNo column in addition to _Dimension and using _DimensionNo for filtering column in such cases

LOAD * Inline [

_Dimension, _DimensionNo

XLR,1

XLR Xigma,2

];

Use the condition

=getselectedcount ( _Measure )>0 and SubStringCount(Concat(_MeasureNo, '|'), '1')

Hope this helps.

Re: Dynamically buidling columns in the straight table-duplicate columns

Gysbert_Wassenaar — Fri, 14 Mar 2014 18:12:29 GMT

And then you find out you have 10 measures and... whoa! substringcount will find that both measureno 1 and 10 contain a '1' character

Re: Dynamically buidling columns in the straight table-duplicate columns

stabben23 — Fri, 14 Mar 2014 18:18:17 GMT

Hi, Be aware that the No 1 and 10 or 2 and 20 and so on could show exactlly the same behavior. I usually use A,B,C,......... in the defined extra dimension. So if you have more than 10 selections use letters.

Re: Dynamically buidling columns in the straight table-duplicate columns

nagaiank — Fri, 14 Mar 2014 18:39:32 GMT

You are correct. I used either _Dimension or _DimensionNo to solve the problem in my application.

Using specified first and last characters in SubStringCount(Concat('^'&_Measure & '^' , '|'), '^XLR^') seems to be a good solution.

Re: Dynamically buidling columns in the straight table-duplicate columns

Fri, 14 Mar 2014 21:51:14 GMT

Thanks all.

I will try the suggested solution and see if that works out.

Re: Dynamically buidling columns in the straight table-duplicate columns

Sat, 15 Mar 2014 01:14:58 GMT

KrishnaMurthy/Gysbert:

Thanks for the help.

Gysbert suggestion was great but it worked only after putting special character as the first and last characters in the SubstringCount.