topic Re: How to split units in one field in QlikView

How to split units in one field

vinod2086 — Fri, 07 Nov 2014 07:44:55 GMT

Hi Friends,

i need one help i have one field like

field with unit

OTHERS

33.512

11 Mbps

2 kbps

40.20 gbps

12 gb

but here i want to split field with unit into[ field value] and [field unit].

Thanks in Advance.

Re: to split units in one field

sushil353 — Fri, 07 Nov 2014 07:49:49 GMT

hi,

It is better if you describe your issue in detail with sample desired output.

Re: to split units in one field

vinod2086 — Fri, 07 Nov 2014 08:01:56 GMT

Hi Sushil,

i want to split single field into two fields like

[field with unit] into 1) [field value] like-- others 11 33.512

2)[field unit]] like mbps kbps gb

am expecting output like that.

Re: to split units in one field

sushil353 — Fri, 07 Nov 2014 08:06:49 GMT

Hi,

You can simply use KeepChar() and purgechar function..

purgechar('absad123',123) returns absad

keepchar('asdf123',123) returns 123

HTH

sushil

Re: How to split units in one field

Anonymous — Fri, 07 Nov 2014 08:34:06 GMT

Hi,

Try with this code:

LOAD fieldwithunit,

KeepChar(fieldwithunit, '1234567890.') as fieldvalue,

Trim(PurgeChar(fieldwithunit, '1234567890.')) as fieldunit

FROM yoursource;

Marc.

Re: How to split units in one field

sujeetsingh — Fri, 07 Nov 2014 08:36:01 GMT

Use subfield() function

Re: How to split units in one field

sujeetsingh — Fri, 07 Nov 2014 08:39:12 GMT

SubField(FieldName, ' ') will give you only number entries.

Re: How to split units in one field

Fri, 07 Nov 2014 08:52:57 GMT

Hi,

Try these two in your load script

SubField(YourField,' ',1) as [Field Value],

SubField(YourField,' ',2) as [Field Unit]

Regards,

Ravikumar

Re: How to split units in one field

IAMDV — Fri, 07 Nov 2014 08:57:50 GMT

If the delimiter is always a space (" ") then you can use the Subfield() function, if not then please provide full data to get more precise answer.

Thanks,

www.QlikShare.com

Re: How to split units in one field

MayilVahanan — Fri, 07 Nov 2014 09:16:39 GMT

Try like this

Load *, SubField(FieldWithUnit,' ', 2) as Value,SubField(FieldWithUnit,' ', 1) as Unit Inline

[
FieldWithUnit
OTHERS
33.512
11 Mbps
2 kbps
40.20 gbps
12 gb
];

Hope it helps

Re: How to split units in one field

vinod2086 — Fri, 07 Nov 2014 10:46:34 GMT

Hello Sushil,

as u mentioned code not giving correct results,

am expecting out put see below table

field_val unit

Others

33.512

11 Mbps

40.20 gbps.

and am not understanding how to use sub field function in this case.

Re: How to split units in one field

Kushal_Chawda — Fri, 07 Nov 2014 12:14:18 GMT

Try

Subfield ([Field value with unit],' ',1) as FieldValue

Subfield ([Field value with unit],' ',2) as Field with unit

Re: How to split units in one field

vinod2086 — Fri, 07 Nov 2014 12:19:19 GMT

...

Re: How to split units in one field

IAMDV — Wed, 14 Jan 2015 14:22:30 GMT

Vinod 4186 wrote:

Hi kush,

i tried above logic but not working

Vinod - How come the above post is the correct answer? Please can you post detailed answer if you know it or mark the correct answer as "correct".

Re: How to split units in one field

its_anandrjs — Wed, 14 Jan 2015 15:03:27 GMT

Hi Vinod,

It seems not good the answer for the question, Some where it is misguide the thread also.

Regards

Anand