Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
antoniotiman
Honored Contributor III

Re: Extracting values from a string

What about this ?

Generic
LOAD Field,'Element'&IterNo() as Field1,SubField(Field,'.',IterNo()) as Field Inline [
Field
xx.xxxx.xxxx.xxxx.xxxx
xxx.xxxxx
a1.a2.a3.a4
BB1.AA1.CD2
LongLongLongSingleTest
]
While IterNo() <= SubStringCount(Field
,'.')+1;

Re: Extracting values from a string

Hi,

another solution might be:

QlikCommunity_Thread_280072_Pic1.JPG

QlikCommunity_Thread_280072_Pic2.JPG

table1:

LOAD key_id,

    Subfield(Left(key_id,Index(key_id,'_',-1)-1),'.') as KeyId1,

    AutoNumber(RowNo(),RecNo()) as KeyId1SeqNo,

    Subfield(key_id,'_',2) as KeyId2

Inline [

    key_id

    xx.xxxx.xxxx.xxxx.xxxx_000

    12.3456.7890.1234.5678_AAA

    123.456.78.90.1.234.56.78_BBBB

    12.34.567890.123456.7890.1.2.3.4.5_CC

];

hope this helps

regards

Marco

lornafnb
Valued Contributor

Re: Extracting values from a string

Antonio,

Please assist me with Syntax?

MySourceTable:

Generic LOAD

key_id,

'Element'&IterNo() as Field1,

SubField(key_id,'.',IterNo()) as Field

Resident

D

While IterNo() <= SubStringCount(Field,'.')+1;

lornafnb
Valued Contributor

Re: Extracting values from a string

hi Marco,

this works very well thanks...but in the following example

I would definitely extract in the the way you provided, EXCEPT for the last part after the last '.'. You will notice that the remaining part I am referring to has this pattern...

xxx_xxx.

so I'd like in the following example

ui.ssa.ocep.product.shop.pillar.product.line.item.controller.from.productlineview.chequeaccounts_easyaccount

I would like the result to be

ui

ssa

ocep

product

shop

pillar

product (again)

line

item

controller

from

productlineview

chequeaccount_easyaccount

can you assist please?

lornafnb
Valued Contributor

Re: Extracting values from a string

Marco,

I see you've already made provision for that (my previous question) with fields Key_id1 and Key_id2, thanks

what does your expression in the pivot table mean? If(Count(key_id),'X')

Re: Extracting values from a string

Hi,

I'm glad it worked.

The expression "If(Count(key_id),'X')" just creates an "X" in the second pivot table to mark existing occurrences of the subfield values in the key_id.


regards


Marco