topic Re: Range Min of Dynamically changing columns in QlikView

Range Min of Dynamically changing columns

Fri, 20 Nov 2015 08:20:29 GMT

Hi,

I have a Dashboard reading from a source where by the list of columns would change Dynamically...

Would need to get the Rangemin of the dynamic kpi columns from this file..

Ex:

Load

Country,

from source_excel;

Now this excel file sometimes might contain:

Country, KPI1, KPI2, KPI3

(or)

Country, KPI1

(or)

Country, KPI1,KPI2,KPI3,KPI4

List of KPI fields changes dynamically.

We would need to find for each country what is he minimum value of these KPI's...If these KPI columns are static then we could do rangemin(column names), but it is dynamic.

Is there a way to perform rangemin based on dynamically changing columns...

Thanks.

Regards,

Qlikshare

Re: Range Min of Dynamically changing columns

effinty2112 — Fri, 20 Nov 2015 09:28:28 GMT

The expression

=Concat({$<$Field = {"*KPI*"}>}$Field,', ')

will give you all the columns names that include the string 'KPI' in the form 'KPI1, KPI2, ...'

You could use that in a rangemin expression.

Re: Range Min of Dynamically changing columns

Kushal_Chawda — Fri, 20 Nov 2015 10:03:17 GMT

Data:

crosstable(KPI,Value,1)

LOAD Country,

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

noconcatenate

LOAD *

Resident Data

where KPI<>'Country' and isnum(Value);

left Join

LOAD Country,

min(Value) as MinValue

Resident Data

Group by Country;

DROP Table Data;

Re: Range Min of Dynamically changing columns

Fri, 20 Nov 2015 10:05:16 GMT

Hi Andrew,

Thanks. I gave =rangemin(Concat({$<$Field = {"*KPI*"}>}$Field,', ')) and table returned no records.

Data:

Load

Country, * from Excel_Source;

//Excel _Source has Country and 3 KPI fields: KPI1, KPI2, KP3.

Straight Table:

Dimension: Country

Expression: =rangemin(Concat({$<$Field = {"*KPI*"}>}$Field,', '))

In above syntax: $Field refers to which one please.

Thanks,

Qlikshare

Re: Range Min of Dynamically changing columns

effinty2112 — Fri, 20 Nov 2015 11:38:19 GMT

Hi qlik share,

Just to check something first, hopefully a step towards the solution create a textbox with the expression

=Concat({$<$Field = {"*KPI*"}>}$Field,', ')

Hopefully it will return the string 'KPI1, KPI2, ... '

Re: Range Min of Dynamically changing columns

Fri, 20 Nov 2015 12:04:55 GMT

‌Thanks Kush. It worked.

Re: Range Min of Dynamically changing columns

Fri, 20 Nov 2015 12:07:02 GMT

‌Thanks Andrew. Did it in the script level. Liked the concat option.

Re: Range Min of Dynamically changing columns

effinty2112 — Fri, 20 Nov 2015 12:11:35 GMT

Make a straight table with country as dimension as this as the expression:

RangeMin($(=Concat({$<$Field = {"*KPI*"}>}$Field,', ')))

From this data

Country	KPI1	KPI2	KPI3	KPI4
Canada	40	30	48	47
India	10	20	64	21
UK	60	70	23	49
US	20	10	55	45

We get

Country	RangeMin(KPI1, KPI2, KPI3, KPI4)

Canada	30
India	10
UK	23
US	10

No scripting required other than a simple load of the data.