topic Re: split Field into FeildValues in Script in QlikView

split Field into FeildValues in Script

Mon, 23 May 2016 10:19:49 GMT

hi,

Is there any possibility to split the Field in script into Field values to rename them Ie., I am loading the data from excel sheet where Feild

is identifier consists like below attachment.But i want to rename them as A for 1st record, B for 2nd and so on how to do.

I have read some post on this subject without to find a solution understandable for me.

thanks in advance.

Re: split Field into FeildValues in Script

jonathandienst — Mon, 23 May 2016 11:35:46 GMT

Field names refer to columns, whereas record numbers refer to rows, so I am confused as to what you are asking. I suggest that you post an example qvw and/or some source data and your desired results.

EDIT please don't upload these as image files.

Re: split Field into FeildValues in Script

trdandamudi — Mon, 23 May 2016 11:40:01 GMT

Can you please give more details with source and expected output....

Re: split Field into FeildValues in Script

DavidFoster1 — Mon, 23 May 2016 12:57:53 GMT

Subfield function based on the point character would allow you to divide up this field, but you would still need to have a subfield for each one you want to split out.

Re: split Field into FeildValues in Script

Tue, 24 May 2016 05:30:07 GMT

hi,

record refer to rows ie., i have field Identifier which is having 5 records which i has to rename like 2.16.840.1.114416.1.24.132565 as A and so on is it possible

Re: split Field into FeildValues in Script

MindaugasBacius — Tue, 24 May 2016 05:43:50 GMT

Try using String functions:

Data:
LOAD Identifier
  ,SubField(Identifier, '.', 1) as SubField1
  ,SubField(Identifier, '.', 2) as SubField2
  ,SubField(Identifier, '.', 3) as SubField3
  ,SubField(Identifier, '.', 4) as SubField4
  ,SubField(Identifier, '.', 5) as SubField5
  ,SubField(Identifier, '.', 6) as SubField6
  ,SubField(Identifier, '.', 7) as SubField7
  ,SubField(Identifier, '.', 😎 as SubField8
  ,Left(Identifier, 17) as Left
  ,Right(Identifier, Len(Identifier) - 18) as Right
  ,Mid(Identifier, 6, 12) as Mid
FROM

(ooxml, embedded labels, table is Sheet1);

The results:

Re: split Field into FeildValues in Script

Tue, 24 May 2016 05:53:03 GMT

hi,

thanks for the response. but what i want is that single record should be renamed as A,B respectively.

Re: split Field into FeildValues in Script

MindaugasBacius — Tue, 24 May 2016 07:00:26 GMT

Could you please give an example?

Let's assume you have a table from one field that contains 6 records:

Identifier

2.16.840.1.114416.1.24.132565

2.16.840.1.114416.1.24.132574

2.16.840.1.114416.1.24.132586

2.16.840.1.114416.1.24.132590

2.16.840.1.114416.1.24.1382597

2.16.840.1.114416.1.24.1382622

What results do you expect?

Re: split Field into FeildValues in Script

DavidFoster1 — Tue, 24 May 2016 09:35:36 GMT

This sounds like a simple apply map on a concatenated/compound key value.

MAP_8PartKey:

LOAD * INLINE [

8PartKey, Name

2.16.840.1.114416.1.24.132565, A

];

and then in your table load you can reference

ApplyMap('MAP_8PartKey',[key field]) as FieldName

Re: split Field into FeildValues in Script

Tue, 24 May 2016 09:46:39 GMT

this is what i am expecting

Identifier

2.16.840.1.114416.1.24.132565 A

2.16.840.1.114416.1.24.132574 B

Identifier

meter A

meter B

Re: split Field into FeildValues in Script

Tue, 24 May 2016 09:50:09 GMT

thanks a lot it is working

Re: split Field into FeildValues in Script

MindaugasBacius — Tue, 24 May 2016 09:56:15 GMT

map_identifier:

Mapping LOAD * INLINE [

F1, F2

2.16.840.1.114416.1.24.132565, A

2.16.840.1.114416.1.24.132574, B

];

Data:

LOAD Identifier

,ApplyMap('map_identifier', Identifier, ':') as Type

INLINE [

Identifier

2.16.840.1.114416.1.24.132565

2.16.840.1.114416.1.24.132574

2.16.840.1.114416.1.24.132586

2.16.840.1.114416.1.24.132590

2.16.840.1.114416.1.24.1382597

2.16.840.1.114416.1.24.1382622

];

The result:

Re: split Field into FeildValues in Script

Tue, 24 May 2016 10:05:20 GMT

thanks a lot it is helpfull

Re: split Field into FeildValues in Script

oknotsen — Tue, 24 May 2016 11:20:58 GMT

If your question is now answered, please flag the Correct Answer.

If not, please let us know what part of this topic you still need help with .

Re: split Field into FeildValues in Script

Kushal_Chawda — Tue, 24 May 2016 11:31:32 GMT

another way

Data:

LOAD *,

Pick(match(Identifier ,'2.16.840.1.114416.1.24.132565','2.16.840.1.114416.1.24.132574'),'A','B') as Type

INLINE [

Identifier

2.16.840.1.114416.1.24.132565

2.16.840.1.114416.1.24.132574

2.16.840.1.114416.1.24.132586

2.16.840.1.114416.1.24.132590

2.16.840.1.114416.1.24.1382597

2.16.840.1.114416.1.24.1382622

];