Qlik Community

Qlik Sense Advanced Authoring

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

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! 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
Highlighted
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.