Delete aggregated records in load script?

[Anonymous]

Hi All,

I am loading Orders data where each Order comprises of several rows of data. Every finished order consists of (at least one) Goods Issue line, plus a Goods Receipt and a Settlement, something like this:

Order #	Transaction	Quantity
101	Goods Issue	25
101	Goods Issue	20
101	Goods Receipt	45
101	Settlement	45

However, my dataset also contains incomplete/WIP orders which will not have a Settlement and/or Receipt.

Does anyone know how I can do a selective load on the aggregated rows which make up one order?

i.e. only load the collective rows which make up an order where the rows contain a Goods Issue, Goods Receipt and a Settlement line.

Thanks in advance for your support,

Keith

[Anonymous]

Hi All,

Pushing this out again in the hope of a response.

Thanks in advance,

Keith

[marcus_sommer · Accepted solution]

You could try something like this:

t:

load Order, Transaction, Quantity From YourDate;

     left join (t)

load

     Order,

     if(wildmatch(concat(Transaction, '+'), '*Goods Issue*') and

        wildmatch(concat(Transaction, '+'), '*Goods Receipt*') and

        wildmatch(concat(Transaction, '+'), '*Settlement*'), 1, 0) as OrderCompleteFlag

Resident t group by Order;

- Marcus

[Anonymous]

Hi Marcus,

This worked perfectly!

I was stumped trying to figure it out so many thanks.

Regards,

Keith

[Anonymous]

One clarification point Marcus, why is the concat(Transaction, '+') required in the wildmatch statement?

I can see that it is key as if removed I get an aggregation error message.

Thanks,

Keith