I think you can solve this using only one load statement.
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
order by TransactionID,
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.
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?
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.
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!