Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

solomon_musayev
New Contributor III

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.



Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Parsing String Fields

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

];

9 Replies
MVP
MVP

Re: Parsing String Fields

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
Honored Contributor III

Re: Parsing String Fields

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

Re: Parsing String Fields

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
Contributor III

Re: Parsing String Fields

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
Valued Contributor

Re: Parsing String Fields

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

Re: Parsing String Fields

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
Honored Contributor III

Re: Parsing String Fields

Sunny,

good idea.

However for Time Field You can use

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

MVP
MVP

Re: Parsing String Fields

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

antoniotiman
Honored Contributor III

Re: Parsing String Fields

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
]
;