topic Re: How to split the data in QlikView

How to split the data

Anonymous — Wed, 12 Sep 2018 05:45:32 GMT

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.

Re: How to split the data

tresB — Wed, 12 Sep 2018 05:54:10 GMT

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

Re: How to split the data

dx_anupam — Wed, 12 Sep 2018 07:11:56 GMT

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

Re: How to split the data

gawalimegha — Wed, 12 Sep 2018 10:29:25 GMT

Hello Suman,

Try this.

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

Re: How to split the data

gawalimegha — Wed, 12 Sep 2018 10:30:04 GMT

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

Re: How to split the data

Anonymous — Wed, 12 Sep 2018 12:46:12 GMT

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.

Re: How to split the data

dx_anupam — Wed, 12 Sep 2018 13:36:58 GMT

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

Re: How to split the data

captain89 — Wed, 12 Sep 2018 15:11:27 GMT

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