Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jbduquesne78
New Contributor II

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

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.

JB

1 Solution

Accepted Solutions
jbduquesne78
New Contributor II

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

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,

JB

9 Replies

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

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;

jbduquesne78
New Contributor II

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

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

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

jbduquesne78
New Contributor II

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

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

Capture d’écran 2018-02-22 à 20.08.23.png

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

Did you look at this guy?

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

jbduquesne78
New Contributor II

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

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


JB

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

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

jbduquesne78
New Contributor II

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

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 ? 

Capture d’écran 2018-02-23 à 21.50.01.png

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,

JB

jbduquesne78
New Contributor II

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

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,

JB

Community Browser