Multiple cross value exclusion

[#1]

I have the need to exclude multiple returns for different values...

Example...

Where we have multiple companies that we are pulling vendor data against...

For Company A, need to exclude its unique vendors 1, 3, 7, 8, 10

For Company B, need to exclude its unique vendors 001, 004, 005, 007

For Company C, need to exclude its unique vendors 111, 115, 117, 119

For Company D, need to exclude its unique vendors 2, 6, 9, 10, 11

The problem being there may be multiple vendors with Vendor ID 1, but within each company, they are unique and different entities, so I may need to only exclude Vendor 1, when its associated to Company A, but not exclude a Vendor 1 if its associated to another company, as its a completely different entity.

Any suggestions on a good way to go about this... thanks.

[ramoncova06]

if you can change your script I would suggest adding a new field called excluded vendors and then just added a 1 or 0

if you have to do it on the front end then you might be able to use the "e" function in your set analysis, though if you could provide some sample of how your data looks in order to verify which option could be better suited for your needs

sum( {$<CompanyA = E({<Vendor={‘1, 3, 7, 8, 10’}>})>} Sales )

[marcus_sommer]

I would try to create a flag-field within the script which contained a 1 for all vendors which are included and 0 by excluded vendors and then you could simply use an expression like sum(value) * VendorFlag.

Within the gui you could use set analysis expressions like: sum{< Vendors -= {1,3,7,8,10}>} value) maybe nested with some if-loops for all other conditions.

- Marcus

[#4]

Would it be possible to do a WHERE NOT(MATCH())?

Where I create a variable called vExclusions, and in it list off the fields I want to exclude....and plug in a joined field such as CompanyVendorID so that each vendor ID  is uniquely set for its company?

For example... if I have

Company = CMI

VendorID = 008720

Create

CompanyVendorID = CMI_008720

In my vExclusions variable I could list   'CMI_008720' and all the others I need to exclude...

My problem is how to script it... I've tried the below and a number of others.. .just not getting the statement to work...

Load



Company &'_'& VendorNum As KEY_CompanyVendorNum,

Company,

Company &'_'& VendorID As CompanyVendorID,

Name as "Vendor Name",

VendorID

    where not(Match(CompanyVendorID,$(vExclusions)));

       

SQL SELECT

   Company,

    Name,

    VendorID,

    VendorNum,

FROM

Epicor905.dbo.Vendor;

[ramoncova06]

what you want to do is create a mapping instead

something like the following

mapping

Excluded:

load * inline

[Key,Value

,CMI_008720,1

]

Load

Company &'_'& VendorNum As KEY_CompanyVendorNum,

Company,

Company &'_'& VendorID As CompanyVendorID,

Name as "Vendor Name",

VendorID

    where applymap('Excluded',Company &'_'& VendorID,'No') ='No';

SQL SELECT

   Company,

    Name,

    VendorID,

    VendorNum,

FROM

Epicor905.dbo.Vendor;

[#6]

Where in qlik does the mapping actually get entered...

And... for that load * inline statement... I'm going to have multiple values... CMI_008720    MCI_5469    TAB_111222 that will be combinations of companies and their respective vendors to be excluded... how do those get listed out.

Thanks,

[ramoncova06]

normally your mapping goes before the loads

do you have a way to identify those values from within the DB or are they are going to be all hard coded values ?

if they are hard coded then you probably need to do some nested if's

[#8]

There are 8 different companies... basically I need to eliminate cross company payments... where a company is a vendor to another one of our companies... as $ is really just being transferred internally as far as corporate sees it...

So.. 56 Vendor entities need to filtered out from all the other vendors...

[ramoncova06]

I would assume that the name(vendor name) and the company should be the same then, right ?

if this is the case this should work

Load

Company &'_'& VendorNum As KEY_CompanyVendorNum,

Company,

Company &'_'& VendorID As CompanyVendorID,

Name as "Vendor Name",

VendorID

where not exists(Company,Name);

SQL SELECT

   Company,

    Name,

    VendorID,

    VendorNum,

FROM

Epicor905.dbo.Vendor;

[#10]

Unfortunately no... some where acquired at different times... they use different ERP systems, so I'm having to extract from 5 different data sources, and they did not name each other the same in their respective Vendor name fields, which is why I had to go thru and create a cross table so I could know what the Vendor ID # was respectively for each of the companies, in each company's system...

For example. if use the

Company &'_'& VendorID option, I will get

For company CMI

I will have vendors

CSI_00943

MCI_00745

TAB_00569

CCS_00653

For company TAB

I will have vendors:

CSI_1123445

MCI_1143563

CMI_1153664

CCS_1167545

and so on...for 8 source companies.