Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Similar function to BELOW?

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Similar function to BELOW?


1.png


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;

3 Replies
MVP & Luminary
MVP & Luminary

Re: Similar function to BELOW?

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.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Similar function to BELOW?


1.png


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;

Not applicable

Re: Similar function to BELOW?

Thank you.. It was helpful