Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jakob_rasmussen
New Contributor III

Date period on log analysis

My organisation would like to be able to monitor the development of status for records in our system. In our case we are talking about purchase orders that can have different statuses assigned throughout it's lifetime. But it could be anything like

Below and attached I have tried to display a simplified version of the table that we'll be using for the analysis (using DD-MM-YYYY):

Log_Analysis.jpg

In above example OrderID 10001 had status B on 04-08-2011. I would like to end up with a QlikView file where I can select Aug 4 and this OrderID and the status type B will be type of the selection.

Right now, I'm pretty clueless on how to set up the Calendar script on such a file. Anyone who can guide me in the right direction?

Jakob Libak Rasmussen

Denmark

Tags (2)
1 Solution

Accepted Solutions
jakob_rasmussen
New Contributor III

Date period on log analysis

Update: I'm still not fully convinced that the above examples will perform with large data sets.

I have been playing around with the IntervalMatch function which basically does the same. In above examples a script line like below has been used.

IntervalMatch ( selection_date ) LOAD StartDate, EndDate RESIDENT Order_source_data

It basically does the same and I'm still worried about performance, but will let it rest for now.

Best regards,

Jakob

7 Replies
dennisnet
Valued Contributor III

Re: Date period on log analysis

Hi Jacob,

Take a look at the atteched QV-file.

I loaded your data and add a calander.

Hope it helpfull.

Good Luck,

Dennis.

jakob_rasmussen
New Contributor III

Date period on log analysis

Thanks Dennis,

It's not quite what I'm looking for. In your example I can select Aug 4 and no corresponding data from the Log table is associated to it. I want to be able to create a snapshot on how the system looked on Aug 4 where the OrderID 10001 had status B.

I hope this clarifies.

Best regards,

Jakob

dennisnet
Valued Contributor III

Re: Date period on log analysis

Hi Jacob,

Take a look at the atteched QV-file.

I loaded your data and add a calander.

Give the first end the last date you want to select and press the button.

A marco select the dates in your datum-field.

If you want you can delete the variable vStartDate from the input box.

Use the line below in your loading script to always set the Start-variabel to the first date in your date-field.

     SET vStartDate = '=min(datum)'                    ;

Hope it helpfull.

Good Luck,

Dennis.

Not applicable

Re: Date period on log analysis

Jakob, I think you must generate new data in you load script to solve your problem. There has to be a record with order 10001, status B and date  04-08-2011 for you to be able to click the way you want.


This data can be probably be generated by qlikview with the autogenerate function and a (few) loops, if not, it can be done in SQL at least.


Regards Robert

Not applicable

Re: Date period on log analysis

Like this attached example. I don't kow if it can be done easier, but at least this is a solution....

jakob_rasmussen
New Contributor III

Date period on log analysis

Hi Robert,

Thanks for taking your time on this. I will play around with your suggestion and see if it can work for us. I'm a bit worried about the performance when used on a source table with more than 15 million records.

Best regards,

Jakob

jakob_rasmussen
New Contributor III

Date period on log analysis

Update: I'm still not fully convinced that the above examples will perform with large data sets.

I have been playing around with the IntervalMatch function which basically does the same. In above examples a script line like below has been used.

IntervalMatch ( selection_date ) LOAD StartDate, EndDate RESIDENT Order_source_data

It basically does the same and I'm still worried about performance, but will let it rest for now.

Best regards,

Jakob

Community Browser