How to split the data

[Anonymous]

Hi experts,

      I have the data with number of symbols and i want to split the data as the output model.

I am attach the excel file.Please give the suggestion.

Thanks in advance.

[tresesco]

Could you explain the logic (generic) how you got the output column?

[dx_anupam]

Hi Suman,

I am assuming

1) you want data from first occurrence of "T"

2) the (HOL), and ($) will always at the end of string

in such scenario below formula may help you out

=subfield(PurgeChar(Right(Rtrim(LTrim(data)),Len(Rtrim(LTrim(data)))-(Index(Rtrim(LTrim(data)),'T')-1)),' .'),'(',1)

Regards,

Anupam

[gawalimegha]

Hello Suman,

Try this.

=PurgeChar(Mid(data,Index(data,'T',1),Len(data)),'($)')

[gawalimegha]

=PurgeChar(Mid(data,Index(data,'T',1),Len(data)),'($).')

[Anonymous]

Hi,anupam,

  Thanks for giving good suggestion and one more of split 11-TS7865HFD-HTGE in this last words  are shows only like HTGE instead of TS7865HFD. Please give suggestion.

Thanks.

[dx_anupam]

Hi Suman,

Just add one more subfield as below

=Subfield(subfield(PurgeChar(Right(Rtrim(LTrim(data)),Len(Rtrim(LTrim(data)))-(Index(Rtrim(LTrim(data)),'T')-1)),' .'),'(',1),'-',1)

IF the answer helped you to solve your issue kindly mark it as correct and helpful.

Regards,

Anupam

[captain89]

Hi you can do it using MapSubString:

ReplaceMap:

mapping LOAD * INLINE [

    char, replace

    (, ""

    ), ""

    ”, ""

    /, ""

    –, ""

    $, ""

    ., ""

    " ", ""

];

TestData:

LOAD

*,

trim(MapSubString('ReplaceMap', mid(data, Index(data, 'T'), len(data)))) as ReplacedString

;

LOAD * INLINE [

    data, OUTPUT

    01-TS7865HFD, TS7865HFD

    02-TS 65HGT, TS65HGT

    03 TG 98HJ(HOL), TG98HJ

    04 TG.78.KJI, TG78KJI

    TS89GHT($), TS89GHT

    TG90GFR, TG90GFR

];

source:

Using MapSubstring() to edit strings | Qlikview Cookbook