E9.1 Limit F42119 Orders Being Pull for Data Analysis

[FrankCLT]

Hello All,

I have built a work file similar to what is shown below. But our managers threw me a curve ball, they want to limit the number of orders to pull for given SHAN. Data is from the F42119.

Let's say that the order limit is 3 orders per SHAN.

For SHAN = 112873, although it shows 6 lines, it is 4 orders. I would need to only write orders 3318732, 3315256, 3308230, and exclude 3305605.

The other samples would be fine, no exclusions.

Anyone do something like this?



Thank you,
FrankCLT

 

[kiran4hari]

With the assumption that you are using an UBE to populate the work follow, you can try following option.

Sequence data on SHAN and DOCO, turn on level breaks on both.
On SHAN level break header Do Section, set Order Count = 0.
On DOCO level break header Do Section , set Order Count = Order Count +1.
In Main Section Do section, insert record to work file when Order Count is <=3. This way only first 3 orders are processed for each SHAN, rest will be ignored.

Only drawback with this option is, your UBE will be slow as it will select and process all orders.

 

[FrankCLT]

Yes, I see where you're getting at...

Thank you so much...
FrankCLT

 

[Stank1964]

For this kind of thing, you really need a SQL statement (I usually create a SQL stored procedure in cases like this).... We need 3 things: a UBE, a powershell script and a table.

First, a UBE controls the action. The UBE calls a power shell script (the request) and the power shell script outputs table records (the response).

Create a power shell script that the UBE will call using the execute external program BSFN. The power shell script always has UKID as a parameter. The power shell script processes the request (perhaps by calling a SQL stored proc) and generates what is the response by adding table records. The table contains the UKID. Build the temp table with E1 and insert rows into it in the stored proc. That way the UBE can read the records and do whatever it is you need to do with them.

Using power shell in this way can be incredibly handy. The sky is the limit.