topic Re: Reading the next Sales Order Line Item in QlikView

Reading the next Sales Order Line Item

Anonymous — Mon, 30 Mar 2015 21:12:03 GMT

Hi All,

In SAP, you have your sales order and sales order line items. In my particular environment, the material # i need to display is always present in the next line item # after the line item # with the material marked specialty stock 'E'. So, i would need to write something like this, I would imagine:

In material # field expression:

if([Spec.Stock] = 'E', return the material # in the next sales order line item #)

Does anybody know of a QV function or any special code/logic that will read the next value in a field after a given condition?

Thanks a ton for your help,

Ron

Re: Reading the next Sales Order Line Item

fred_s — Mon, 30 Mar 2015 21:18:47 GMT

You could use the below() function.

Grtz Fred

Re: Reading the next Sales Order Line Item

Anonymous — Tue, 31 Mar 2015 00:05:03 GMT

Thanks, Fred.

I have applied the following expression and am currently getting the material on the first line of the next sales order (I'm looking for the next line item on the same sales order):

if(SpecStock = 'E', below(Mat., 1))

Does anyone have a suggestion for aggregating this to use Line Item as a dimension? i also imagine a pick() might be involved, possibly?

Thank you,

Ron

Re: Reading the next Sales Order Line Item

fred_s — Tue, 31 Mar 2015 08:05:49 GMT

Hi Ron,

Maybe you should consider to split your table.

Is it possible to show us an example of your data / your tables?

Pref. as attachment.

Grtz Fred

Re: Reading the next Sales Order Line Item

Anonymous — Tue, 31 Mar 2015 13:58:29 GMT

Hi Fred,

I'm attaching a qvw. I'll illustrate with a few screenshots (i didn't trim 0's from the data in QV):

The Material in Line Item 10 in the below screenshot is marked as Specialty Stock 'E'. Sometimes, another "specialty stock" material can be found on Line Item 33 or so as well. But, the material I am after is on the Line Item number right below the material marked Specialty Stock 'E' 100% of the time. Regardless if 'E' is on line 10 or on line 53, i need the material in the line right below it within the same Sales Order.

Using the above formula if(SpecStock = 'E', below(Mat., 1)); I have managed to bring the Material in line 10 of the next Sales Order when 'E' is selected. I'm really after the material in the next line item after the material marked Specialty Stock 'E', regardless of user selections. I think there has to be some sort of dimensional selection on line item, but I could be very wrong.

This Specialty Stock can be found on a lot of different line items, so forcing line 11 with set analysis won't help me in this case:

Thanks a ton for your help, as i really can't come up with anything to solve this problem. I'm sure just an expression in the front-end will solve it.

Thanks again,

Ron

Re: Reading the next Sales Order Line Item

fred_s — Tue, 31 Mar 2015 14:07:02 GMT

Hi Ron,

I'll look into it, but it may take approx 4 hours.

Have to finish a couple of jobs, drive home (and traffic in windy Netherlands is a bit of a mess at the moment) and having dinner...

Grtz

Re: Reading the next Sales Order Line Item

Anonymous — Tue, 31 Mar 2015 14:23:43 GMT

Fred,

Be careful in traffic. Please take your time and don't worry about it if you can't get around to it tonight. I just really appreciate your willingness to help me with this issue. If Sales Orders were entered in the system correctly every time, I could just force select line 11 with a simple set analysis. Unfortunately, it can never be that easy with most enterprise data

Thank you,

Ron

Re: Reading the next Sales Order Line Item

Tue, 31 Mar 2015 19:10:35 GMT

Hi Ron,

Suppose we have excel of this type:-

Store		Sales_Order_Line_Item
A		Pen
B		Brush
C		Car
E		Pencil
D		Book
F		Cap

Now suppose we have to create a table such that if Store=E then pick the next Sales_Order_Line_Item which is Book otherwise pick the same Sales_Order_Line_Item. Resultant table will look like:-

Store	Sales_Order_Line_Item
A	Pen
B	Brush
C	Car
E	Book
D	Book
F	Cap

Required Code is:-

Data:

LOAD Store,

Sales_Order_Line_Item

FROM

(ooxml, embedded labels, table is Sheet1);

Count:

load

Count(Store) as Count

Resident Data;

let vTest = Peek('Count',0,'Count');

for i=1 to $(vTest)

Data1:

load

peek('Store',$(i)-1,'Data') as Store,

if ( peek('Store',$(i)-1,'Data')='E',peek('Sales_Order_Line_Item',$(i),'Data'),peek('Sales_Order_Line_Item',$(i)-1,'Data')) as [Sales Order Line Item1]

AutoGenerate 1;

next;

Please revert if there are any challenges.

Thanks and Regards

Jasmeet

Re: Reading the next Sales Order Line Item

fred_s — Wed, 01 Apr 2015 19:42:55 GMT

Hi Ron,

Attached a Qlikview file with Materials created in the script, using the Previous() function.

Grtz Fred

Re: Reading the next Sales Order Line Item

Anonymous — Fri, 03 Apr 2015 13:46:12 GMT

Thanks a whole lot, Fred. Above(), Below(), and Previous() are new ideas to me. I'll try to look up some more examples and learn these function's behavior. This solution works great. Thanks again.

- Ron