topic Re: extracting info from a row in a csv. in QlikView

extracting info from a row in a csv.

Wed, 15 May 2013 09:54:17 GMT

Hello there

I have the following problem, i have a table with a column that looks like this

1. BMC Patrol Agent 3.9.00 on srv001601

2. Red Hat KVM Server 83 running on srv001352.mud.internal.co.za

3. Microsoft Hyper-V Server 2008 R2 SP1 identified as SRV001543 on srv001502

4. OdLetters-1.0.17:doc-was7-32-02

The only information I would like to extract from this is the srv_name, eg:

srv001601

srv001352

srv001502

the rest would be 'other'

This would be loaded as a list box with the srv_name and extra field 'other', where 'other' is the rows without a srv_name in them (e.g: row 4)

As you can see the srv_name is placed in diferrent instances in the data and you can have more than 1 srv in 1 row of data thus i cannot select text beggining with srv (eg: row3).

What I have noticed is that the srv_name I want is always after the word 'on', if thats any help.

Please help.

Re: extracting info from a row in a csv.

Gysbert_Wassenaar — Wed, 15 May 2013 10:02:46 GMT

Try: if(index(MyField, 'on srv'), mid(MyField, index(MyField, 'on srv')+3,9),'other') as ServerName

edit: index returns the start position of the first occurence of a search string in a string. If MyField contains 'on srv' then index(MyField, 'on srv') returns a number larger than 0. In that case we know that there's a server number in the string. If there isn't we specify 'other' as result of the if function. To get the server number the mid function is used. It needs a starting position and a length to determine the substring that's extracted from MyField. The starting position in again calculated with the index function, but we add 3 so we start at srv instead of on.

Re: extracting info from a row in a csv.

Wed, 15 May 2013 10:12:17 GMT

Gysbert

Wow, thank you so much! That absolutely resolved my problem.

If its not any trouble, could you please explain the logic behind this as i may run into something similar i n the future.

I'm fairly new to qlikview developing.

Thanks