Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Delete aggregated records in load script?

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 #TransactionQuantity
101Goods Issue25
101Goods Issue20
101Goods Receipt45
101Settlement45

  

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
Anonymous
Not applicable
Author

Hi All,

Pushing this out again in the hope of a response.

Thanks in advance,

Keith

marcus_sommer

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
Not applicable
Author

Hi Marcus,

This worked perfectly!

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

Regards,

Keith

Anonymous
Not applicable
Author

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