Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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