Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
jaymeknapp
New Contributor

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 (2)
3 Replies
jaymeknapp
New Contributor

Re: Excluding a group of transactions based on items in those transactions

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

Jayme

MVP
MVP

Re: Excluding a group of transactions based on items in those transactions

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
New Contributor

Re: Excluding a group of transactions based on items in those transactions

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.