topic How can I solve this? in QlikView

How can I solve this?

Fri, 02 Sep 2011 19:41:58 GMT

Hello Qlik Community!

I want to do something but I don't know how to realize it. Although I might have a solution for it, I don't really think it's the right one ...

So here it goes:

I have a transaction log with the following fields:

TransactionID, Person, Cashier, Time, Flag

The Flag can be either true or false.

What I want to do is to add an column to this Table named "status"

A transaction with the flag 'true' will get in the status field '1'

A transaction with the flag 'false' will get '0' IF there is a transaction with a 'true' flag following it within 30 minutes (for the same cashier and person)

and '-1' if there isn't such a transaction.

I think this could be done pretty straightforward using a couple of for loops, but I don't know how, or if it is possible.

What I have tried so far is to find out how to calculate the status.

I filtered out all transactions with a 'false' flag and added them a column with their timestamp + 1/2 hour. (it took me a while to find out how to manipulate a timestamp -_-)

Finally i joined them on an intervalmatch with all the 'true' transactions.

[zeros]:

LOAD

person,

cashier,

time

Resident [log]

Where flag = 'true';

Left join intervalMatch(Time)

Load

transactionID,

person,

cashier,

time as timeStart,

Timestamp(time + 30/(24*60), 'DD-MM-YYYY hh:mm:ss') as timeEnd

Resident [log]

Where flag = 'false'

This is not really working as it joins me every 'true' of 'false' transaction with the following 'true' transaction (with the same person and casheer)

This redundacy is not acceptable as the log table is huge.

After getting this far I asked myself if it could be done without creating other redundant tables for it.

So how would you do it?

Thanks in advance!!1

How can I solve this?

fosuzuki — Wed, 07 Sep 2011 18:18:10 GMT

I think you can solve this using only one load statement.

Something like:

Load TransactionID,

Person,

Cashier,

Time,

Flag,

if(Flag='true',1, if(peek('TransactionID')=TransactionID and peek('Person')=Person and peek('Cashier')=Cashier and peek('Flag')='false' and [peek('Time')-Time< 30min], 0, -1) as Status

from x

order by TransactionID,

Person,

Cashier,

Time;

please note that the if statement I created is just a sample and you must adjust it. The time difference check is just conceptual, you must code it correctly.

Hope this points you in the right direction.

Regards,

Fernando

How can I solve this?

Wed, 07 Sep 2011 21:02:47 GMT

Hello and thanks for your response,

I have tried the code but it is not working (even without the time calculation).

If I understood it right, peek is only checking the previous record. But in my case I have to iterate through all transactions to find out if there is somewhere a 'true' transaction at the right time with the right person and cashier.

These last days I tried to do this using macros.

Sadly I can't find a documentation for available functions other than the automation reference which doesn't say much.

Trying to fight my way through vbscript, some questions popped out:

1. Is there a way to make a SELECT with the DynamicUpdateCommand ? at least to see if it found something.

I tried to put the select statement into an exists(), but the DynamicUpdateCommand has something against that.

2. Can I iterate somehow through the table without using .select and .GetPossibleValues to move through the columns as these are pretty time consuming?

Thanks!

How can I solve this?

fosuzuki — Thu, 08 Sep 2011 13:48:24 GMT

Can u send a xls sheet with some sample data? I think this should be very easy to solve in the script.

You should always avoid using macros since they are a lot more time consuming. And if you have a time consuming calculation, try to always precalculate them in the script, so that users can have a nice experience.

Regards,
Fernando

How can I solve this?

Thu, 08 Sep 2011 20:19:55 GMT

Yes that's true, I noticed how much it got slowed down by the macros!

Thanks for your help but it the precalculation by using the script won't be necessary anymore.

You were right about the precalculation so I took it even further and precalculated the whole table in sql before loading it into qlikview!

Thanks again for your help!