topic Re: Customisable table problem in QlikView

Customisable table problem

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have developed a customisable table allowing users to select dimensions and metrics from a list.

I am loading the following Excel table in my script -

_metrics	_metricsNo	%_metricsgroup
Number of enrolments	1	Activity
Number of credits	2	Activity
% of total enrolments	3	Activity
% of total credits	4	Activity
% completed successful	5	Success
Number completed successful	6	Success
% partial success	7	Success
Number partial success	8	Success
% further withdrawal	9	Success
Number further withdrawal	10	Success
% early withdrawal	11	Success
Number early withdrawal	12	Success

I am using the conditional checkbox for the expression and (as an example for Metric number 12) using the following expression -

SubStringCount(Concat(_metricsNo, '|'), '12')

However, whenever a user selects metrics number 10,11,12 the table also shows metric 1 and 2. How do I change the expression to avoid that?

Thanks

Greg

Re: Customisable table problem

prieper — Fri, 15 Sep 2017 08:54:18 GMT

Format with _metricNo with two digits: NUM(_metricNO, '00') should do

Re: Customisable table problem

sam_grounds — Fri, 15 Sep 2017 09:01:36 GMT

I also have created customizable pivot table objects using a similar concept.

What I find works well is creating list boxes which are presented a toggle-able (check boxes) and then using this as the expression for the condition for each dimension (or expression, I have choices for each).

GetFieldSelections(_metrics) LIKE '*Number of enrolments*' OR GetFieldSelections(_metrics) LIKE '*ALL*'

Put a version of this with each metric name within the first LIKE criteria.

The drawback with this method is that when the selections default to 'NOT Number of enrolments' because all but that are selected, it breaks it, but I don't find people tend to use that many metrics, so it works well.

Also, I'd set a pivot to always fully expanded. That works best.

Hope this helps,

Sam

Re: Customisable table problem

patroser — Fri, 15 Sep 2017 09:02:27 GMT

Hi Greg,

we usually wrap the ID in non-printables (chr(30)).

let vSep=chr(30);

LOAD (...),

$(vSep) & _noMetrics & $(vSep)as _noMetrics

You need to wrap your _metricsNo in those non-printables within script and can access them from UI as following:

SubStringCount(Concat(Dim), vSep & '12' & vSep )

Hope this helps.

Regards,
Patrick

Re: Customisable table problem

sebastianlettner — Fri, 15 Sep 2017 09:06:29 GMT

Hallo,

the problem is, that if you select metric 10 you Concat-String looks like '10'.

SubstringCount('10', '1') is true.

Just add a separator around each metricNo (e.g. chr(30))

Now you can use SubstringCount(Concat(MetricNo), chr(30) & '1' & chr(30)) (chr(30) = record separator)

If you select metric 10 your Concat String looks like '[RS]10[RS]' and SubstringCount('[RS]10[RS]', '[RS]1[RS]') is false

Regards

Sebastian

Re: Customisable table problem

Kushal_Chawda — Fri, 15 Sep 2017 09:18:21 GMT

try with below expressions for each metrics

1) To display metrics with number 10

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|10|*')

2) To display metrics with number 11

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|11|*')

3) To display metrics with number 12

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|12|*')

4) To display metrics with number 1

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|1|*')

like wise for all the expression number