Previous Rule in loop

[qlikviewaf]

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!!

[sasikanth]

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"

[qlikviewaf]

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"

[sasikanth]

can you share sample data ??

[qlikviewaf]

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

[sasikanth]

"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)??

[qlikviewaf]

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

[sasikanth · Accepted solution]

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

[qlikviewaf]

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

[sasikanth]

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'