topic parsing string in QlikView

parsing string

Tue, 31 Jan 2012 11:53:12 GMT

hi all,

i am wondering if it is possible to read specific digits from a string during the load.

the pattern of the string is always the same - means:

i want to read all numbers from the first - (if followed by a number) up to the first .

example:

somestringheredoesntsaymuch556-areacode12345-newyork-8797.3652

in this string i would want to extract 8797

what would be the proper way to doe this during the script load ?

thanks a lot.

parsing string

kaushiknsolanki — Tue, 31 Jan 2012 12:01:32 GMT

Hi,

Try this.

Tables:

LOAD * INLINE [
TablesName
somestringheredoesntsaymuch556-areacode12345-newyork-8797.3652

];

data:
Load mid(TablesName,index( TablesName, '-', 3 )+1,4) as abc
Resident Tables;

Regards,

Kaushik Solanki

parsing string

Tue, 31 Jan 2012 12:16:59 GMT

Thanks, but this does not do the job for all strings i have.

The number to extract does not always start after the 3rd -

Sometimes after the 2nd or after the 4th -

But the right - is ALWAYS followed by a number.

Your approach would f.e. not work for something like this:

some-stringheredoesntsaymuch556-areacode12345-newyork-8797.3652

Can you help ?

parsing string

vijay_iitkgp — Tue, 31 Jan 2012 12:29:00 GMT

Hi,

If it is always right number then you can use following. Use fieldname instead of value below

=Subfield('some-stringheredoesntsaymuch556-areacode12345-newyork-8797.3652','-',Substringcount('some-stringheredoesntsaymuch556-areacode12345-newyork-8797.3652','-')+1)

parsing string

Jason_Michaelides — Tue, 31 Jan 2012 15:34:25 GMT

Or I think -1 in SubField() gets the last one, so:

Floor(SubField([YourStringField],'-',-1))

Floor() gets the integer part of the number, so 8797 from 8797.3652

SubField() breaks up the string using '-' as the delimiter and -1 gets the last substring.

Hope this helps,

Jason

parsing string

Thu, 02 Feb 2012 11:51:06 GMT

I have done it this way:

Tables:

Load

TablesName,

if (Chunk = 'No ID', Chunk, TextBetween(Chunk, '-', '.') ) as ID

;

Load

TablesName,

if (IsNum( mid(TablesName, index(TablesName, '-', 3)+1,1) ), mid(TablesName,index( TablesName, '-', 3 )), 'No ID' ) as Chunk

;

thanks!

parsing string

Jason_Michaelides — Thu, 02 Feb 2012 11:56:40 GMT

Well as long as it works! Your solution seems a little over-engineered, and seems to count on a certain number/position of the hyphens, which I thought you couldn't do.

Glad you sorted it.