Prepare data from SAP export

[ulrich_schumann]

Hi all,

we have a regular SAP Export where we have data grouped into sections.

- a first line starts with the Purchase Order number

- then 1 to N lines will follow with the Order Position and the Purchase details

- then further Purchase Order number

- followed by further Purchase Order Positions

How can I combine this Purchase Order number with the following Purchase Order Positions?

Example

Order number 1234

POS 10 - order amount - order date

POS 20 - order amount - order date

Order number 5678

POS 10 - Order amount - order date

...

Any ideas?

[MarqueeCrew · Accepted solution]

@ulrich_schumann

I would consider using the DVS ACS-SAP Connector :)  a shameless plug.

By use of a multi-row formula, you can find rows containing the Order Number and create a new column.  Here is the formula that I used:

IIF(contains([Description],'Order number'),
ToNumber(GetWord([Description], 2)),
[Row-1:Order Number])

It will create a number by finding the third word (get word function starts with word 0) when the term, "Order Number" is found.  If it isn't found, then it uses the Order_Number from the prior row.

I hope that this helps you.

Cheers,

Mark