Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

lornafnb
Valued Contributor

Extracting values from a string

hi guys,

I have the following values in a string in my data...

xx.xxxx.xxxx.xxxx.xxxx_000

I can recall that somewhere in the load statement, there is a function that you can almost recursively, extract every part delimited by a '.'

1. what is that function?

2. I need to extract the part after the '_'  as a separate field. How to do that?

Thanks

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Extracting values from a string

Perhaps like this:

// an example inline table.

MySourceTable:

LOAD * INLINE [

MyStringHere

abc

abc.def

abc.def.ghi

def.ghi

pqr.stu.vwx

klm.nop.qrs.tuv

];

// first calculate the largest number of dots in the strings in the table

tmpNoOfParts:

LOAD max(SubStringCount( MyStringHere, '.')) +1 as NoOfParts RESIDENT MySourceTable;

// put that maximum value in a variable

LET vNoOfParts = peek('NoOfParts',-1,'tmpNoOfParts');

// clean up

DROP TABLE tmpNoOfParts;

// create a variable that will be filled to contain all the field creation definitions for the new fields

SET vFieldList = SubField( MyStringHere , '.',1) as Part_1;

// loop through the number dots

FOR i=2 to $(vNoOfParts)

     LET vFieldList = '$(vFieldList), SubField( MyStringHere,'  & chr(39) & '.'  & chr(39) & ' , $(i)) as Part_$(i)';

NEXT

// load the table using the vFieldList variable to load the fields for the parts separated by dots

MyTable:      

LOAD

     MyStringHere,

     $(vFieldList)

RESIDENT

     MySourceTable

     ;


talk is cheap, supply exceeds demand
15 Replies
MVP & Luminary
MVP & Luminary

Re: Extracting values from a string

1. That's the subfield function. But it may not do what you want in this case. If you'd use subfield(MyString, '.') as SplitField then you'd get a record for each . delimited substring. Not separate fields.

2. Subfield( MyStringHere, '_', -1)


talk is cheap, supply exceeds demand
Highlighted
antoniotiman
Honored Contributor III

Re: Extracting values from a string

Try this

LOAD Subfield(SubField(Field,'.'),'_') as Field Inline [
Field
xx.xxxx.xxxx.xxxx.xxxx_000
]
;

lornafnb
Valued Contributor

Re: Extracting values from a string

Thanks Antonio,

Please explain to me what this means…the ‘Inner’ Subfield looks for (the last?) ‘.’, and then within that looks for the subfield containing ‘_’?

antoniotiman
Honored Contributor III

Re: Extracting values from a string

Yes Lorna,

'_' it creates '000' value in field,

SubField without 3rd parameter (by using only in Script) creates many value of Field according to separator '.'.

antoniotiman
Honored Contributor III

Re: Extracting values from a string

Also,

last value created by SubField(Field,'.') is xxxx_000.

SubField(....,'_') without 3rd parameter create 2 Rows/Values  -> xxxx and 000

lornafnb
Valued Contributor

Re: Extracting values from a string

Thank you

My string is of variable length…

It could be…

xx.xx or

xx.xxxxxxx.xxxxx

I want to ‘loop’ through the string, find every fieldvalue as you indicated, but I need to STOP when I’m at the end of the string….

How would I do this?

antoniotiman
Honored Contributor III

Re: Extracting values from a string

Length and number of separators are not relevant

LOAD Subfield(SubField(Field,'.'),'_') as Field Inline [
Field
xx.xxxx.xxxx.xxxx.xxxx_000
xxx.xxxxx
]
;

lornafnb
Valued Contributor

Re: Extracting values from a string

hi Gysbert,

Thanks for that.

I've split down my code like this...but the problem is, i dont know how long my string will be and how many '.' there will be - I need some 'dynamic' logic so that doesnt matter how long my string is, the script doesnt fail.

SubField(key_id,'.',1) as 1,

SubField(key_id,'.',2) as 2,

SubField(key_id,'.',3) as 3,

SubField(key_id,'.',4) as 4,

SubField(key_id,'.',5) as 5,

SubField(key_id,'.',6) as 6

MVP & Luminary
MVP & Luminary

Re: Extracting values from a string

Perhaps like this:

// an example inline table.

MySourceTable:

LOAD * INLINE [

MyStringHere

abc

abc.def

abc.def.ghi

def.ghi

pqr.stu.vwx

klm.nop.qrs.tuv

];

// first calculate the largest number of dots in the strings in the table

tmpNoOfParts:

LOAD max(SubStringCount( MyStringHere, '.')) +1 as NoOfParts RESIDENT MySourceTable;

// put that maximum value in a variable

LET vNoOfParts = peek('NoOfParts',-1,'tmpNoOfParts');

// clean up

DROP TABLE tmpNoOfParts;

// create a variable that will be filled to contain all the field creation definitions for the new fields

SET vFieldList = SubField( MyStringHere , '.',1) as Part_1;

// loop through the number dots

FOR i=2 to $(vNoOfParts)

     LET vFieldList = '$(vFieldList), SubField( MyStringHere,'  & chr(39) & '.'  & chr(39) & ' , $(i)) as Part_$(i)';

NEXT

// load the table using the vFieldList variable to load the fields for the parts separated by dots

MyTable:      

LOAD

     MyStringHere,

     $(vFieldList)

RESIDENT

     MySourceTable

     ;


talk is cheap, supply exceeds demand