topic Re: Issue in Load Script to parse and stock Date in a Log File in QlikView

Issue in Load Script to parse and stock Date in a Log File

Anonymous — Thu, 22 Feb 2018 01:36:47 GMT

Hello,

My Log file has the following syntax :

9:22:26 (ibmratl) TIMESTAMP 2/9/2018

9:29:08 (ibmratl) IN: "DOORS"

9:31:29 (ibmratl) OUT: "DOORS"

9:32:08 (ibmratl) OUT: "DOORS"

9:36:59 (ibmratl) OUT: "DOORS"

10:58:25 (ibmratl) OUT: "DOORS"

13:44:10 (ibmratl) OUT: "DOORS"

9:22:26 (ibmratl) TIMESTAMP 2/10/2018

9:29:08 (ibmratl) IN: "DOORS"

9:31:29 (ibmratl) OUT: "DOORS"

9:32:08 (ibmratl) OUT: "DOORS"

9:36:59 (ibmratl) OUT: "DOORS"

10:58:25 (ibmratl) OUT: "DOORS"

13:44:10 (ibmratl) OUT: "DOORS"

While the date is not changed, I'd Like to stock dynamically the last Date value in a variable or something like that to create the date filter

I Have coded in LOAD script like that :

LOAD

rowno()                                                                              as [LineNumber],
if(index(@1, ':') > 0, time(round(@1 ,1/24), 'hh:mm'))      as [Time],
replace(replace(@2, '(' , ''), ')', '')                                       as [Vendor],
if(right(@3, 1) = ':', purgechar(@3, ':'))                              as [Action],
if(right(@3, 1) = ':', @4)                                                     as [Feature],

FROM

[./lmgrd.log]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Could you help to add the corresponding date please ?

I'd like to have an output table like that :

2/9/2018 9:29:08 (ibmratl) IN: "DOORS"

2/9/2018 9:31:29 (ibmratl) OUT: "DOORS"

2/9/2018 9:32:08 (ibmratl) OUT: "DOORS"

2/9/2018 9:36:59 (ibmratl) OUT: "DOORS"

2/9/2018 10:58:25 (ibmratl) OUT: "DOORS"

2/9/2018 13:44:10 (ibmratl) OUT: "DOORS"

2/10/2018 9:29:08 (ibmratl) IN: "DOORS"

2/10/2018 9:31:29 (ibmratl) OUT: "DOORS"

2/10/2018 9:32:08 (ibmratl) OUT: "DOORS"

2/10/2018 9:36:59 (ibmratl) OUT: "DOORS"

2/10/2018 10:58:25 (ibmratl) OUT: "DOORS"

2/10/2018 13:44:10 (ibmratl) OUT: "DOORS"

Thank in advance.

Re: Issue in Load Script to parse and stock Date in a Log File

sunny_talwar — Thu, 22 Feb 2018 17:35:08 GMT

Something along these lines

Table:

LOAD *,

RowNo() as [LineNumber],

If(Len(Trim(TempDate)) = 0, Peek('Date'), TempDate) as Date;

LOAD If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [Time],

Replace(Replace(@2, '(' , ''), ')', '') as [Vendor],

If(Right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],

If(Right(@3, 1) = ':', @4) as [Feature],

If(Right(@3, 1) <> ':', @4) as [TempDate];

LOAD * INLINE [

@1, @2, @3, @4

9:22:26, (ibmratl), TIMESTAMP, 2/9/2018

9:29:08, (ibmratl), IN:, DOORS

9:31:29, (ibmratl), OUT:, DOORS

9:32:08, (ibmratl), OUT:, DOORS

9:36:59, (ibmratl), OUT:, DOORS

10:58:25, (ibmratl), OUT:, DOORS

13:44:10, (ibmratl), OUT:, DOORS

9:22:26, (ibmratl), TIMESTAMP, 2/10/2018

9:29:08, (ibmratl), IN:, DOORS

9:31:29, (ibmratl), OUT:, DOORS

9:32:08, (ibmratl), OUT:, DOORS

9:36:59, (ibmratl), OUT:, DOORS

10:58:25, (ibmratl), OUT:, DOORS

13:44:10, (ibmratl), OUT:, DOORS

];

DROP Field TempDate;

Re: Issue in Load Script to parse and stock Date in a Log File

Anonymous — Thu, 22 Feb 2018 18:50:15 GMT

Hello,

Thank you for your help.

But how to do that from the log file directly ?

FROM

[./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Re: Issue in Load Script to parse and stock Date in a Log File

sunny_talwar — Thu, 22 Feb 2018 19:03:02 GMT

Should be something like this

LOAD *,

RowNo() as [LineNumber],

If(Len(Trim(TempDate)) = 0, Peek('Date'), TempDate) as Date;

LOAD If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [Time],

Replace(Replace(@2, '(' , ''), ')', '') as [Vendor],

If(Right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],

If(Right(@3, 1) = ':', @4) as [Feature],

If(Right(@3, 1) <> ':', @4) as [TempDate]

FROM [./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Re: Issue in Load Script to parse and stock Date in a Log File

Anonymous — Thu, 22 Feb 2018 19:09:54 GMT

I Think there is any problem with tempDate and Date field.

Re: Issue in Load Script to parse and stock Date in a Log File

sunny_talwar — Thu, 22 Feb 2018 19:12:57 GMT

Did you look at this guy?

If(Right(@3, 1) <> ':', @4) as [TempDate]

Re: Issue in Load Script to parse and stock Date in a Log File

Anonymous — Thu, 22 Feb 2018 23:51:09 GMT

Yes I looked this instructions. And I changed it by the following line :

If(Index(@3, 'TIMESTAMP') > 0, @4) as [TempDate]

And now it's working good !

Thank you for your help.

I didn't know that you can use a first LOAD to run some action as you can see in scripting (Perl, Python ...)

Well, it stay another one problem.

In log file, you can see the time restart at 0:00.

But if no TIMESTAMP instructions at 0:00, it takes the last value, the "yesterday date value".

21:50:11 (ibmratl) IN: "DOORS"

22:01:01 (ibmratl) IN: "DOORS"

22:09:01 (ibmratl) OUT: "DOORS"

22:14:12 (ibmratl) OUT: "DOORS"

//new day

0:03:01 (ibmratl) IN: "DOORS"

0:06:12 (ibmratl) IN: "DOORS"

0:11:01 (ibmratl) OUT: "DOORS"

0:32:12 (ibmratl) OUT: "DOORS"

2:07:02 (ibmratl) IN: "DOORS"

2:12:13 (ibmratl) IN: "DOORS"

2:15:01 (ibmratl) OUT: "DOORS"

2:34:13 (ibmratl) OUT: "DOORS"

3:17:27 (lmgrd) TIMESTAMP 2/10/2018

3:37:31 (ibmratl) TIMESTAMP 2/10/2018

4:09:02 (ibmratl) IN: "DOORS"

4:17:02 (ibmratl) OUT: "DOORS"

So, I think to concatenate date and time to convert it to timestamp.

And I'll raise comparaison between the last timestamp value and the new one.

if newtimestamp < last value = new value

if newtimestamp > last value = keep current value

What do you think about it ?

Have you an idea how to that in the code ?

Thank you

Re: Issue in Load Script to parse and stock Date in a Log File

sunny_talwar — Fri, 23 Feb 2018 02:54:10 GMT

To create a timestamp, all you need to do is Date + Time....

So, in the context of your app, you can do this

TimeStamp(Date + Time) as TimeStamp

Re: Issue in Load Script to parse and stock Date in a Log File

Anonymous — Fri, 23 Feb 2018 21:03:26 GMT

Yes, it works. Thank you !

I Have a last problem.

Between The line 140 and ligne 147, I have an issue.

If you compare the "current timestamp" with the "previous timestamp", it will done for the line 140 but not after.

How to stock a Max Timestamp value and compare with the field value ?

And if the field value is < MaxTimestamp = Date + 1 , else no change ?

Here my code. If you can see optimization, don't hesitate to modify.

LOAD *,

RowNo() as [LineNumber],
     If(Len(Trim(TempDate)) = 0
     , Peek('Date')
     , TempDate
     ) as [Date],

      If(Len(Trim(TempDate)) = 0
     , Timestamp(Peek('Date') + Time)
     , Timestamp(TempDate + Time)
     )   as [Date/Time],


      If(Len(Trim(TempDate)) = 0
     , Peek('Date') + Time
     , TempDate + Time
     )   as [Timestamp]

;

LOAD

If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [TimeHour],
If(Index(@1, ':') > 0, @1) as [Time],
     Replace(Replace(@2, '(' , ''), ')', '') as [Vendor],
     If(Right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],
if(right(@3, 1) = ':', @4) as [Feature],
if(right(@3, 1) = ':' AND (purgechar(@3, ':') = 'IN' OR purgechar(@3, ':') = 'OUT'), subfield(@5, '@', 1)) as [User],
if(right(@3, 1) = ':' AND (purgechar(@3, ':') = 'IN' OR purgechar(@3, ':') = 'OUT'), subfield(@5, '@', -1)) as [Client],
if(index(@3, 'OUT') > 0, '1', if(index(@3, 'OUT') = 0 AND index(@3, 'IN') = 0, '0', '-1')) as [Counter],
     If(Index(@3, 'TIMESTAMP') > 0, date(date#(@4, 'MM/DD/YYYY'), 'DD/MM/YYYY')) as [TempDate]

FROM [./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Thank in advance,

Re: Issue in Load Script to parse and stock Date in a Log File

Anonymous — Sat, 24 Feb 2018 00:44:13 GMT

Hello,

It's good. I found solution.

Here is my Code :

Thank for your help !

Tell me if you think to add some optimization in the following code :

Log:

LOAD *,

RowNo() as [LineNumber],
Timestamp(Date + Time) as [Date/Time]
;

LOAD *,
If([Old_Timestamp] < Peek([Old_Timestamp])
, date(date#([Old_Date], 'DD/MM/YYYY') + 1)
, If(Len(Trim(Peek('Date'))) = 0
, [Old_Date]
, Peek('Date'))) as [Date]
;

LOAD *,

     If(Len(Trim(TempDate)) = 0
     , Peek('Date')
     , TempDate
     ) as [Old_Date],

      If(Len(Trim(TempDate)) = 0
     , Timestamp(Peek('Date') + Time)
     , Timestamp(TempDate + Time)
     )   as [Old_Date/Time],

      If(Len(Trim(TempDate)) = 0
     , Peek('Date') + Time
     , TempDate + Time
     ) as [Old_Timestamp]
;

LOAD

If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [TimeHour],
If(Index(@1, ':') > 0, @1) as [Time],
     PurgeChar(@2, '(,)') as [Vendor],
     If(right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],
If(right(@3, 1) = ':', @4) as [Feature],
If(right(@3, 1) = ':' AND (Purgechar(@3, ':') = 'IN' OR Purgechar(@3, ':') = 'OUT'), subfield(@5, '@', 1)) as [User],
If(right(@3, 1) = ':' AND (Purgechar(@3, ':') = 'IN' OR Purgechar(@3, ':') = 'OUT'), subfield(@5, '@', -1)) as [Client],
If(index(@3, 'OUT') > 0, '1', '0') as [Counter],

     If(Index(@3, 'TIMESTAMP') > 0
     , date(date#(@4, 'MM/DD/YYYY'), 'DD/MM/YYYY')
     , If(Index(@3, 'FLEXnet') > 0
         , date(date#(Purgechar(@14, '(,)'), 'MM/DD/YYYY'), 'DD/MM/YYYY')
     ) ) as [TempDate]

FROM [./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

DROP FIELD [TempDate], [Old_Date], [Old_Date], [Old_Date/Time];

Cheers,