topic Delete aggregated records in load script? in QlikView

Delete aggregated records in load script?

Anonymous — Wed, 10 Aug 2016 07:44:36 GMT

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

Re: Delete aggregated records in load script?

Anonymous — Tue, 23 Aug 2016 07:25:55 GMT

Hi All,

Pushing this out again in the hope of a response.

Thanks in advance,

Keith

Re: Delete aggregated records in load script?

marcus_sommer — Tue, 23 Aug 2016 08:02:37 GMT

You could try something like this:

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

Re: Delete aggregated records in load script?

Anonymous — Wed, 24 Aug 2016 10:21:35 GMT

Hi Marcus,

This worked perfectly!

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

Regards,

Keith

Re: Delete aggregated records in load script?

Anonymous — Wed, 24 Aug 2016 10:30:24 GMT

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