topic Re: Extract field values & DateTime in QlikView

Extract field values & DateTime

prees959 — Sat, 16 Nov 2024 18:21:01 GMT

HI, Im reading some log files where each row starts as a datetime and an IP address:

Can someone advise please on how to split the field into the following data items :

Many thanks!

Phil

Re: Extract field values & DateTime

Frank_Hartmann — Fri, 17 Jul 2020 08:50:43 GMT

see attached

hope this helps

Re: Extract field values & DateTime

Taoufiq_Zarra — Fri, 17 Jul 2020 09:01:26 GMT

One solution :

Data: load Date(TimeStamp#(replace(subfield(A,']',1),'[',''),'YYYYMMDD hh:mm:ss')) as Date, Time(TimeStamp#(replace(subfield(A,']',1),'[',''),'YYYYMMDD hh:mm:ss')) as Time, replace(subfield(A,']',2),'[','') as IP, subfield(A,']',3) as DESC FROM .\Downloads\dsds.xlsx (ooxml, explicit labels, table is Feuil1);

Input:

output :

Re: Extract field values & DateTime

Kushal_Chawda — Fri, 17 Jul 2020 10:58:07 GMT

Assuming that you have same format of the other values. You can do below

If format of the time in string is different then you need to provide same format in timestamp# function.

Data: Load *, date(floor(timestamp#(TextBetween(Field,'[',']'),'YYYYMMDD hh:mm:ss')),'DD/MM/YYYY') as Date, time(timestamp#(TextBetween(Field,'[',']'),'YYYYMMDD hh:mm:ss')) as Time, TextBetween(Field,'[',']',2) as IP, SubField(Field,']',-1) as DESC Inline " Field [20200303 14:00:00] [86.245.198.170] southern stores 588872 "