topic Extract substring of values based upon the different delimeter in QlikView

Extract substring of values based upon the different delimeter

Anonymous — Fri, 07 Apr 2017 21:23:40 GMT

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

Re: Extract substring of values based upon the different delimeter

MarcoWedel — Sat, 08 Apr 2017 00:19:58 GMT

Hi,

one solution could be:

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

Re: Extract substring of values based upon the different delimeter

Kushal_Chawda — Sat, 08 Apr 2017 05:06:09 GMT

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

];

Re: Extract substring of values based upon the different delimeter

Anonymous — Thu, 13 Apr 2017 22:05:14 GMT

Thanks, its working fine.

Re: Extract substring of values based upon the different delimeter

Anonymous — Thu, 13 Apr 2017 22:06:53 GMT

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