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: 
jaymeknapp
Contributor III
Contributor III

Excluding a group of transactions based on items in those transactions

Greetings,

I'm trying to create a data load process that will exclude a group of transactions that contain specific item types.

My 1st Step is a binary load:

BINARY [lib://Qlik Applications/6d6d79a0-a8c6-4f99-bc7e-fd79e49778ee];

 

My 2nd Step is to filter the data:

Inner Join(Transactions)
LOAD
%TRANSACTION_KEY
Resident Transactions WHERE [Transaction Order Status]='Closed'
and [Transaction Status]='Billed'
and [Transaction Ship Complete]='T'
;

Inner Join(Fact)
LOAD
%TRANSACTION_KEY
Resident Transactions WHERE [Transaction Order Status]='Closed'
and [Transaction Status]='Billed'
and [Transaction Ship Complete]='T'
;

Inner Join(Items)
LOAD
%ITEMID_KEY
Resident Items WHERE [Items Type Name]='Assembly/Bill of Materials'
and [Items Item Category]='Catalog'
and [Items Production Method] <> 'Make-to-Order' ;

Inner Join(Fact)
LOAD
%ITEMID_KEY
Resident Items WHERE [Items Type Name]='Assembly/Bill of Materials'
and [Items Item Category]='Catalog'
and [Items Production Method] <> 'Make-to-Order' ;

 

I'm just not sure how I exclude those (Transactions) WHERE  [Items Production Method] <> 'Make-to-Order' .

I have it in as a filter, but all that does is remove the items from the over all data.  I want to remove any Transaction that has those Make-to-Order items.

Any direction would be greatly appreciated!

Jayme Knapp

Labels (3)
3 Replies
jaymeknapp
Contributor III
Contributor III
Author

I'm still looking for any help I can get on this item....

Jayme

jonathandienst
Partner - Champion III
Partner - Champion III

i m not sure what the problem is - your script looks OK, unless the [Items Production Method] field is not in table Items. In that case, you will need to join the [Items Production Method] field to Items before applying the Item filter.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jaymeknapp
Contributor III
Contributor III
Author

The [Items Production Method] is part of the Items table, but the problem is, when I filter out (at the item table level), it just removes those items, but keeps the overall transaction, if that transaction also contains an MTS item.  What I need to do is remove those transactions that contain an MTO item, even if the transaction contains MTS items too.