Date period on log analysis

[jakob_rasmussen]

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

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

[Anonymous]

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]

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

[Anonymous]

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.

[#5]

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

[#6]

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

[jakob_rasmussen]

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 · Accepted solution]

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