topic Re: Similar function to BELOW? in QlikView

Similar function to BELOW?

Fri, 18 Sep 2015 14:19:54 GMT

Hi.

I have a Scenario, could some one help me how to do it using Qlikview.

In the attached file, I have 3 columns to determine the result column. Please let me know whether we can do this using scripting or as a expression.

Scenario:

If the first ID and the ID in the next line matches, Version is 1, Order is A and the next order is B or D then Result is Fruit.

else if the version number in next row is summation of Version+1 and order is C and Order in next line is B then it is a Vegetable else Salads.

ie: as per example quoted here,

if (100 =100 (Next Line), If(Version=1 and Order=A and Order(Next Line)=B or D, 'Fruits',

if(Version(Next Line)= Version+1 and Order=C and Order(next Line)=B,'Vegetables'),'Salads').

How can i fetch the next line data of a column? Can someone solve this?

Is it best to use expressions in straight table or is there a way to do it via scripting?

Suggest me!

Thanks!

Gysbert_Wassenaar — Fri, 18 Sep 2015 17:13:11 GMT

How can i fetch the next line data of a column? Can someone solve this?

Reverse the order of the records with an Order By clause and then use the Previous() function.

maxgro — Fri, 18 Sep 2015 17:29:05 GMT

source:

load * inline [

ID, Version, Order, Result

100,1,A,Fruits

100,1,B,Salads

200,1,B,Salads

400,1,C,Vegetables

400,2,B,Salads

];

final:

NoConcatenate LOAD

if(ID=Previous(ID) and Previous(Version)=1 and Order='A' and match(Previous(Order),'B','D'), 'Fruit',

if(Previous(Version)=(Version+1) and Order='C' and Previous(Order)='B', 'Vegetables',

'Salad'

)) as NeweResult

Resident source

order by ID desc, Version desc, Order desc;

DROP Table source;

Mon, 21 Sep 2015 10:39:36 GMT

Thank you.. It was helpful