Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi All,
Pushing this out again in the hope of a response.
Thanks in advance,
Keith
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
Hi Marcus,
This worked perfectly!
I was stumped trying to figure it out so many thanks.
Regards,
Keith
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