topic Re: Previous Rule in loop in QlikView

Previous Rule in loop

qlikviewaf — Fri, 25 Jun 2021 09:31:03 GMT

Hi Guys,

i have following data set.

I would like to create a new field, called in the example below "IK|LocationRule" that shows the value "Consignment" if the prevoius record is "Fly". Basically i want that IK|LocationRUle cannot be never Fly, if the starting location is "Consignment". Can be fly only if the starting location (26/05/2021) is different than "Consignment"

I set this rule:

IF(Location&Previous(Location)='FlyConsignment','Consignment',Location) as IK|LocationRule

The rule is working just for the first record (27/05/2021) but i would like to have also the other days as Consignment.

How i can fix this? Any Idea?
Thanks!!

Re: Previous Rule in loop

sasikanth — Fri, 25 Jun 2021 10:17:10 GMT

HI,

Try below condition

if(isnull(Previous(Location)),Location, if(Previous(Location)='Fly','Consignment',if(Previous(Location)<>'Consignment','Fly',Location))) as IK|LocationRule

above condition is based on below note

*"IK|LocationRule" that shows the value "Consignment" if the previous record is "Fly"

Re: Previous Rule in loop

qlikviewaf — Fri, 25 Jun 2021 10:41:31 GMT

This is not working since the "Starting location" (26/05/2021) could be also "Warehouse" and in that case we need to stick with "Fly"

Re: Previous Rule in loop

sasikanth — Fri, 25 Jun 2021 11:48:14 GMT

can you share sample data ??

Re: Previous Rule in loop

qlikviewaf — Fri, 25 Jun 2021 12:21:38 GMT

Find attached the QVW- i expect to have in the IK|LocationRule , "Consignment" on the highlighted cell in RED, since the starting location is Consignment

Re: Previous Rule in loop

sasikanth — Fri, 25 Jun 2021 12:46:51 GMT

"since the starting location is Consignment"

what is the starting location you were referring to ?? is it based on Month??

how do you define starting location ??

what are you expecting for "Fly" (27.05.2021 to 31.05.2021)??

What are you expecting for "Fly" (13.06.2021 to 15.06.2021)??

What are you expecting for "Warehouse" ( 07.06.2021 to 11.06.2021)??

Re: Previous Rule in loop

qlikviewaf — Fri, 25 Jun 2021 12:53:49 GMT

Let me say it better: the requirement is to have "Fly" as value, only if the Previous location was not Consignment , but needs to be applied also for the following days.

So for the 27 May, since the 26 is Consignment (this is what i called starting location), the 27-31 cannot be Fly but needs to be Consignment

For 13.06 to 15.06, i expect to stick with Fly, since the previous location was Warehosue (11 June)

What are you expecting for "Warehouse" ( 07.06.2021 to 11.06.2021)?? Nothing, if it's warehouse needs to stick to warehouse

Re: Previous Rule in loop

sasikanth — Fri, 25 Jun 2021 15:08:59 GMT

its clear now,

use the below condition to achieve the desired results

if(Location='Fly' and peek(IK|LocationRule)='Consignment','Consignment',Location) as IK|LocationRule

Hope this helps

Sasi

Re: Previous Rule in loop

qlikviewaf — Fri, 25 Jun 2021 15:22:49 GMT

it works! can you explain logic behind that rule if you dont mind ?

Re: Previous Rule in loop

sasikanth — Fri, 25 Jun 2021 16:19:24 GMT

simple,

Location field it self is not sufficient to derive 'consignment' value for all 'Fly' rows, peek function is used to retrieve previous value of a field which can be used in the same condition.

peek(IK|LocationRule) --> returns previous value in IK|LocationRule field .

previous value in IK|LocationRule field for Location ='Fly' is 'Consignment'