Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rpayn01215
Creator
Creator

Reading the next Sales Order Line Item

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

1 Solution

Accepted Solutions
fred_s
Partner - Specialist
Partner - Specialist

Hi Ron,

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

Grtz Fred

View solution in original post

9 Replies
fred_s
Partner - Specialist
Partner - Specialist

You could use the below() function.

Grtz Fred

rpayn01215
Creator
Creator
Author

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


fred_s
Partner - Specialist
Partner - Specialist

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

rpayn01215
Creator
Creator
Author

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

fred_s
Partner - Specialist
Partner - Specialist

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

rpayn01215
Creator
Creator
Author

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

Not applicable

Hi Ron,

Suppose we have excel of this type:-

StoreSales_Order_Line_Item
APen
BBrush
CCar
EPencil
DBook
FCap

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

StoreSales_Order_Line_Item
APen
BBrush
CCar
EBook
DBook
FCap

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

fred_s
Partner - Specialist
Partner - Specialist

Hi Ron,

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

Grtz Fred

rpayn01215
Creator
Creator
Author

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