Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shaksmintu
Contributor III
Contributor III

Extract substring of values based upon the different delimeter

Hi All,

I have values like as below.

Description

----------------

106:Undef

1379.UpperProvidence

Aloha Channel 1378.5250

I need to extract the ID and their description separately from above data set.

Expected Result:

ID   |  Description

-------------------------

106      Undef

1379    UpperProvidence

5250    Aloha Channel 1378

How we can achieve this? Can anyone give me the suggestion for that.

If similar kind of data set we can achieve by using subfield().

Regards,

Shakila Desamuthu

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_256448_Pic1.JPG

table1:

LOAD If(Index(Description,':'),SubField(Description,':',1),If(Index(Description,'.'),If(IsNum(SubField(Description,'.',1)),SubField(Description,'.',1),SubField(Description,'.',2)))) as ID,

    If(Index(Description,':'),SubField(Description,':',2),If(Index(Description,'.'),If(IsNum(SubField(Description,'.',1)),SubField(Description,'.',2),SubField(Description,'.',1)))) as Description

INLINE [

    Description

    106:Undef

    1379.UpperProvidence

    Aloha Channel 1378.5250

];

hope this helps

regards

Marco

View solution in original post

4 Replies
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_256448_Pic1.JPG

table1:

LOAD If(Index(Description,':'),SubField(Description,':',1),If(Index(Description,'.'),If(IsNum(SubField(Description,'.',1)),SubField(Description,'.',1),SubField(Description,'.',2)))) as ID,

    If(Index(Description,':'),SubField(Description,':',2),If(Index(Description,'.'),If(IsNum(SubField(Description,'.',1)),SubField(Description,'.',2),SubField(Description,'.',1)))) as Description

INLINE [

    Description

    106:Undef

    1379.UpperProvidence

    Aloha Channel 1378.5250

];

hope this helps

regards

Marco

View solution in original post

Kush
MVP
MVP

another way could be

Map:

mapping LOAD * Inline [

TextFrom, TextTo

:,-

.,- ];

table1:

LOAD *, if(isnum(SubField(DescriptionNew,'-',1)),SubField(DescriptionNew,'-',1),if(isnum(SubField(DescriptionNew,'-',2)),SubField(DescriptionNew,'-',2))) as ID,

              if(IsText(SubField(DescriptionNew,'-',1)),SubField(DescriptionNew,'-',1),if(IsText(SubField(DescriptionNew,'-',2)),SubField(DescriptionNew,'-',2))) as DescriptionFinal;

LOAD * , MapSubString('Map',Description) as DescriptionNew

INLINE [

    Description

    106:Undef

    1379.UpperProvidence

    Aloha Channel 1378.5250

];

shaksmintu
Contributor III
Contributor III
Author

Thanks, its working fine.

shaksmintu
Contributor III
Contributor III
Author

Hey, this logic too satisfying. Thanks for the update.