Range Min of Dynamically changing columns

[#1]

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

[effinty2112]

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.

[Kushal_Chawda · Accepted solution]

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;

[#4]

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

[effinty2112]

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, ... '

[#6]

‌Thanks Kush. It worked.

[#7]

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

[effinty2112]

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.