Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Parsing String Fields

Hello

I am bringing a field called Textline2 with the following string structure

AT CSICU ON 05/30/15 AT 1141 BY TestUser.

I would like to parse the above string into the following columns

Location          Date                      Time              User

CSICU            5/50/15                   1141               TestUser

I have tried using the subfield function but my results are parsing as follows

String

AT CSICU ON 05/30/15 AT 1141 BY TestUser

.

Code                                                                                                 My Result

   subfield(TextLine2, 'AT', 2 ) AS Location,                                             CSICU ON 05/30/15

   subfield(TextLine2, 'ON', 2 ) AS Date                                                   05/30/15 AT 1141 BY TestUser

    subfield(TextLine2, 'AT', 3 ) AS Time,                                                   1141 BY TestUser



Please help.



1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:

LOAD Trim(TextBetween(F1, 'AT', 'ON')) as Location,

Trim(TextBetween(F1, 'ON', 'AT')) as Date,

Trim(SubField(Mid(F1, Index(F1, 'AT', 2) + 2), 'BY', 1)) as Time,

Trim(SubField(F1, 'BY', -1)) as User;

LOAD * INLINE [

    F1

    AT CSICU ON 05/30/15 AT 1141 BY TestUser

];

View solution in original post

9 Replies
sunny_talwar

May be this

Table:

LOAD Trim(TextBetween(F1, 'AT', 'ON')) as Location,

Trim(TextBetween(F1, 'ON', 'AT')) as Date,

Trim(SubField(Mid(F1, Index(F1, 'AT', 2) + 2), 'BY', 1)) as Time,

Trim(SubField(F1, 'BY', -1)) as User;

LOAD * INLINE [

    F1

    AT CSICU ON 05/30/15 AT 1141 BY TestUser

];

antoniotiman
Master III
Master III

Hi Salamon,

maybe this

LOAD
SubField(Field,' ',2) as Location,
SubField(Field,' ',4) as Date,
SubField(Field,' ',6) as Time,
SubField(Field,' ',-1) as User
Inline [ Field
AT CSICU ON 05/30/15 AT 1141 BY TestUser
]
;

Regards,

Antonio

its_anandrjs

Try wit subfield function here with combination

LOAD *,

SubField(Str,' ',1) as Location,

SubField(Str,' ',2) as 2,

SubField(Str,' ',3) as 3,

SubField(Str,' ',4) as Date,

SubField(Str,' ',5) as 5,

SubField(Str,' ',6) as Time,

SubField(Str,' ',7) as 7,

SubField(Str,' ',8) as 8;

LOAD Str

FROM

C:\Users\Home\Desktop\Qcomm\StrTable.xlsx

(ooxml, embedded labels, table is Sheet1);

Img3.PNG

kuczynska
Creator III
Creator III

You  can try a combination of subfield & replace functions too:

test:

load * Inline [

Textline

AT CSICU ON 05/30/15 AT 1141 BY TestUser

];

test1:

Load

updated_text,

subfield(updated_text, '//', 2) as location,

    subfield(updated_text, '//', 3) as date,

    subfield(updated_text, '//', 4) as time,

    subfield(updated_text, '//', 5) as user

;

Load

replace(replace( replace(Textline, 'AT', '//') , 'ON', '//'), 'BY' , '//') as updated_text

resident test;

brunobertels
Master
Master

Hi

not sure of my approche and not tested but

Try with space character as delimiter :

subfield(TextLine2,' ',2) as Location,

subfield (TextLine2,' ',3) as time ,

subfield(textline2,' ',4) as Date,

etc

its_anandrjs

Finally as your requirement load this ways

LOAD *,

Second&' '&Third&' '&Date as LocationFinal,

Date &' '& Location&' '&Time &' '&Seventh &' '& Eight as DateFinal,

Time &' '& Eight as TimeFinal;

LOAD *,

SubField(Str,' ',1) as Location,

SubField(Str,' ',2) as Second,

SubField(Str,' ',3) as Third,

SubField(Str,' ',4) as Date,

SubField(Str,' ',5) as Fifth,

SubField(Str,' ',6) as Time,

SubField(Str,' ',7) as Seventh,

SubField(Str,' ',8) as Eight;

LOAD Str

FROM

C:\Users\Home\Desktop\Qcomm\StrTable.xlsx

(ooxml, embedded labels, table is Sheet1);

antoniotiman
Master III
Master III

Sunny,

good idea.

However for Time Field You can use

TextBetween(Field,'AT ',' BY',2) as Time,

sunny_talwar

I tried that, but for some reason it did not work for me... Did it work for you?

antoniotiman
Master III
Master III

Yes,Sunny.

(You can add Trim)

LOAD
TextBetween(Field,'AT ',' ON') as Location,
TextBetween(Field,'ON ',' AT') as Date,
TextBetween(Field,'AT ',' BY',2) as Time,
SubField(Field,'BY',-1) as User
Inline [ Field
AT CSICU ON 05/30/15 AT 1141 BY TestUser
]
;