topic Re: Split part in Qlikview in QlikView

Split part in Qlikview

Tue, 21 Jul 2015 06:11:11 GMT

Hi,

I have data something like keerthi(3222).

I need to display Keerthi and 3222 separately.

Please help me with this

Thanks,

Keerthi KS

Re: Split part in Qlikview

Ralf-Narfeldt — Tue, 21 Jul 2015 06:14:00 GMT

If the field is called Input:

SubField(Input,'(', 1) gives Keerthi

http://help.qlik.com/sense/2.0/en-US/online/#../Subsystems/Hub/Content/Scripting/StringFunctions/SubField.htm

TextBetween(Input, '(', ')') gives 3222

http://help.qlik.com/sense/2.0/en-US/online/#../Subsystems/Hub/Content/Scripting/StringFunctions/TextBetween.htm

Re: Split part in Qlikview

MayilVahanan — Tue, 21 Jul 2015 06:16:16 GMT

Try like this

SubField('keerthi(3222)','(',1)

SubField(FieldName,'(',1)

TextBetween('keerthi(3222)','(',')')

TextBetween(FieldName,'(',')')

Re: Split part in Qlikview

PrashantSangle — Tue, 21 Jul 2015 06:21:01 GMT

Hi,

There is various ways to acheive it.

one way is already suggested by Ralf

another can be

Purgechar(fieldName,'()0123456789') as field1

KeepChar(Purgechar(fieldName,'()'),'0123456789') as field2

Regards

Re: Split part in Qlikview

jagan — Tue, 21 Jul 2015 06:27:12 GMT

Hi,

You can use SubField() and TextBetween() for this, do this in script for better performance

SubField('keerthi(3222)','(',1)

TextBetween('keerthi(3222)','(',')')

Hope this helps you.

Regards,

Jagan.

Re: Split part in Qlikview

Tue, 21 Jul 2015 06:42:24 GMT

Hi Jagan,

I have Fiscal Year 2015-16 which should be displayed as a Label for some amount.

There are other Fiscal Years like 2010-11, 2011-12 etc.

By default it should display the latest Fiscal Year i.e 2015-16

Re: Split part in Qlikview

Tue, 21 Jul 2015 06:57:41 GMT

What if my data is something like Keerthi (Raj) & Kumar (Raj) (2345)

I need to display Keerthi (Raj) & Kumar (Raj) and 2345.

Basically at the there is a number within ( ). I need only that sepaartely

Re: Split part in Qlikview

PrashantSangle — Tue, 21 Jul 2015 06:57:56 GMT

Hi,

Try

max(Fiscal_Year)

Regards

Re: Split part in Qlikview

Tue, 21 Jul 2015 07:04:11 GMT

I tried with Max it shows '-'. Because Fiscal year is something like 2015-16 not a number

Re: Split part in Qlikview

sasiparupudi1 — Tue, 21 Jul 2015 07:06:29 GMT

try maxstring(Fiscal_Year)

Re: Split part in Qlikview

Tue, 21 Jul 2015 07:09:22 GMT

Thanks Sasidhar. Its been a year working on Qlikview and forgot most of the functions.

Re: Split part in Qlikview

PrashantSangle — Tue, 21 Jul 2015 07:11:51 GMT

Hi,

You can use maxstring()

Regards

Re: Split part in Qlikview

Kushal_Chawda — Tue, 21 Jul 2015 07:26:55 GMT

As your field may be not fixed better approach would be as follows

Load *,

mid(Data,1,Index(Data,'(',-1)-1) as Name,

mid(Data,Index(Data,'(',-1)+1,Index(Data,')',-1)-(Index(Data,'(',-1)+1)) as Number

Inline [

Data

Keerthi (Raj) & Kumar (Raj) (234511)

];

Re: Split part in Qlikview

MayilVahanan — Tue, 21 Jul 2015 07:44:26 GMT

Hope, the number occur at last.

LOAD *, Left(Data,FindOneOf(Data, Num)-2) as Start;

LOAD *, KeepChar(Data, 1234567890) AS Num Inline

[

Data

Keerthi (Raj) & Kumar (Raj) (2345)

];

Re: Split part in Qlikview

Ralf-Narfeldt — Tue, 21 Jul 2015 07:47:10 GMT

A bit more complicated. Assumption is that there is a (1234) value at the end of each field value. If this is not true, it gets even more complicated.

PurgeChar(SubField(Input, '(', -1), ')') gives 2345

Left(Input, Index(Input, '(', -1) -1) gives Keerthi (Raj) & Kumar (Raj)

It's always hard to make this kind of cleaning without seeing a full data set, to see all patterns.

Re: Split part in Qlikview

sasiparupudi1 — Tue, 21 Jul 2015 07:50:39 GMT

Hi Keerthi,

It looks like everybody is using their imaginations and giving many solutions to the posed question..Wouldnt it be better if you loaded a sample data file for us?

Sasi

Re: Split part in Qlikview

Tue, 21 Jul 2015 08:07:25 GMT

Please find the attached for data. Number at the end should get separately.

In the sample data. There is a name(Full name along with partner name) followed by Unit number.

I want the name and Unit number separately

Re: Split part in Qlikview

MayilVahanan — Tue, 21 Jul 2015 08:15:16 GMT

Try like this

LOAD *, If(Isnull(Num) or Len(Num) = 0, Data, Left(Data,FindOneOf(Data, Num)-2)) as Start;

LOAD *, KeepChar(Data, 1234567890) AS Num Inline

[

Data

Keerthi (Raj) & Kumar (Raj) (2345)

Keerthi

];

Re: Split part in Qlikview

Tue, 21 Jul 2015 08:22:44 GMT

Please find the attached QVW

Re: Split part in Qlikview

jagan — Tue, 21 Jul 2015 08:24:40 GMT

Hi Keerthi,

Who will create sample data for the attached image? Instead of attaching the screenshot of the data, you can attach direct values in Excel or any other format. It is always good to attach data in excel and your expected values against it.

So that it would be easier to understand the requirement and minimizes the number of replies.

Check below link, hope it helps you in getting the answers faster.

Qlik Community Tip: Posting Successful Discussion Threads

Regards,

Jagan.