topic Re: Create a Loop in Qlikview in QlikView

Create a Loop in Qlikview

Mon, 29 Apr 2013 13:57:53 GMT

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.

Re: Create a Loop in Qlikview

Gysbert_Wassenaar — Mon, 29 Apr 2013 15:45:52 GMT

See attached example.

Re: Create a Loop in Qlikview

Tue, 30 Apr 2013 06:23:46 GMT

Great, i think this is nearly that what we want. But we have one issue which complicates these script. We have no sorting/groups of the payments. So we have to start with every payment, which has no refID.

    Value,      Start,           End,           ID,      REFID

    150,         1.7.2007,,                    2,          1
    200,         1.6.2006,,                    1,
    100,         1.2.2009,,                    3,           2
    200,         1.6.2013,,                    4,          3

    105,         15.2.2009,,                    3,          2
    155,         15.7.2007,,                    2,          1
    205,         15.6.2006,,                    1,
    205,         15.6.2013,,                    4,          3
    105,         15.6.2009,,                    3,          2

]

Re: Create a Loop in Qlikview

Thu, 02 May 2013 09:33:36 GMT

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



]