Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
we have a table with a no specified amount of payments. These payments are connected with a ref id to the item before.
Now we need to get the valid item from the payments. The problem is that the valid one is not not allways the last one.
A payment has a begin and end date. A series of payments can have items with begin and end dates in the past and the future.
for example:
Value Start End ID REFID
200 1.6.2013 4 3
100 1.2.2009 3 2
150 1.7.2007 2 1
200 1.6.2006 1 Null
The problem is how we can get the correct item for today (ID 3)
In the CRM where we use the database, we have an loop where we start with first item with ID Null. Then we look for a item with REFID equal ID and if we found an item, we look if this it is valid with the start and end date. Then we look for the next one, because you can overwrite a valid payment with an other valid payment. If we didn't find a next valid payment this is our valid payment.
The question is: How we can create a loop like this in Qlikview.
Thank you for help.
See attached example.
Perhaps this could be a better sample. The underlined rows are one payment and the not underlined are a second payment. Now he should start at every payment with no REFID.
:
Value, Start, End, ID, REFID
200, 1.6.2006,, 1,
205, 15.6.2006,, 2,
105, 15.2.2009,, 3, 2
150, 1.7.2007,, 20, 1
100, 1.2.2009,, 30, 20
200, 1.6.2013,, 40, 30
205, 15.6.2013,, 400, 3
]