topic Re: How to find the smallest number in a string in QlikView

How to find the smallest number in a string

Mon, 16 Jan 2012 06:11:14 GMT

Hello

I have a list of unique keys and a string of numbers separated by '~'.

Uniquekey	VisitWeek
1002122	16.2~14.1~20.3~22.2~27.2~30.2~24.3~32.2~36.1~34.2~37.2~38.1~39.1~40.4~40.2
1003818	16~20.6~27~29~33~35~35.3~35.2~35.6
1000186	31.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4
1006542	19.5~22~30~28
1016978	40~41.1~25~28~30~35~36~36~38~39

I need to locate the smallest number in the string (like below). Is it possible?

Uniquekey	VisitWeek
1002122	14.1
1003818	16
1000186	13
1006542	19.5
1016978	25

Thanks in advance.

How to find the smallest number in a string

vijay_iitkgp — Mon, 16 Jan 2012 06:32:29 GMT

Hi You can use following script:

T1:
Load
Uniquekey,
Min(Visitweek)
Group By Uniquekey;

LOAD Uniquekey,
Subfield(VisitWeek,'~') as Visitweek
FROM

(ooxml, embedded labels, table is Sheet1);

How to find the smallest number in a string

Mon, 16 Jan 2012 09:36:41 GMT

Hi,

If your unique key is going to have only one visit week entry then above will do fine but if it is not then you can add visitweek also in group by.

So, like this way, you can do. I have taken your table as inline table, so used resident. Just try this.

T1:

LOAD * INLINE [

UniqueKey, VisitWeek

1002122, 16.2~14.1~20.3~22.2~27.2~30.2~24.3~32.2~36.1~34.2~37.2~38.1~39.1~40.4~40.2

1003818, 16~20.6~27~29~33~35~35.3~35.2~35.6

1000186, 31.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4

1006542, 19.5~22~30~28

1016978, 40~41.1~25~28~30~35~36~36~38~39

];

T2:

LOAD

UniqueKey,

VisitWeek,

min(VisitWeek_temp) as Min_VisitWeek Group by UniqueKey,VisitWeek;

LOAD

UniqueKey,

SubField(VisitWeek,'~') as VisitWeek_temp,

VisitWeek

Resident T1;

DROP Table T1;

Hope this helps.

Regards,

Ashutosh

How to find the smallest number in a string

Anonymous — Mon, 16 Jan 2012 14:07:42 GMT

If you want do it on the front end, in chart expression, function rangemin() is what you need. Just replace the '-' with comma in the strings - you can use replace() function for this.

Re: How to find the smallest number in a string

Miguel_Angel_Baeyens — Mon, 16 Jan 2012 16:02:15 GMT

Hi,

Elaborating Michael's suggestion, the following expression should work either in the script or in the front end, returning a table with a new field "MinPerUniquekey" with the minimum value of VisitWeek separated by "~" for each Uniquekey. The RangeMin() should work as an expression in a chart with Uniquekey as dimension.

DataTemp:
LOAD * INLINE [
Uniquekey, VisitWeek
1002122, 16.2~14.1~20.3~22.2~27.2~30.2~24.3~32.2~36.1~34.2~37.2~38.1~39.1~40.4~40.2
1003818, 16~20.6~27~29~33~35~35.3~35.2~35.6
1000186, 31.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4
1006542, 19.5~22~30~28
1016978, 40~41.1~25~28~30~35~36~36~38~39
];
MinTemp:
LEFT JOIN (DataTemp) LOAD Uniquekey,
     RangeMin(SubField(Concat(VisitWeek, ','), '~')) AS MinPerUniquekey
RESIDENT DataTemp
GROUP BY Uniquekey;

Hope that helps.

Miguel

Re: How to find the smallest number in a string

Mon, 16 Jan 2012 22:15:16 GMT

Thank you for your suggested answers.

I have used Min(SubField(VisitWeek,'~')) in my expression to get the answer. It works perfectly fine in expression. When I have tried to use this in dimension and it didn't work though.

Cheers

ptsang1011