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]

ok,under this scenario I would consider the values as hard coded since you do not have an automatic way to identify them within each ERP

use the mapping inline load as the first part of your script and then just exclude them with the where condition

mapping

Excluded:

load * inline

[Key,Value

CMI_008720,1

CSI_00943,1

MCI_00745,1

TAB_00569,1

CCS_00653,1

CSI_1123445,1

MCI_1143563,1

CMI_1153664,1

CCS_1167545,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;

I just realized that you could also use the where not exist, and since you are removing them completely from your data set I would actually recommend this:

mapping

Excluded:

load * inline

[Excluded,Value

CMI_008720,1

CSI_00943,1

MCI_00745,1

TAB_00569,1

CCS_00653,1

CSI_1123445,1

MCI_1143563,1

CMI_1153664,1

CCS_1167545,1

];

Load

Company &'_'& VendorNum As KEY_CompanyVendorNum,

Company,

Company &'_'& VendorID As CompanyVendorID,

Name as "Vendor Name",

VendorID

    where not exists (Excluded,Company &'_'& VendorID) ;

SQL SELECT

   Company,

    Name,

    VendorID,

    VendorNum

FROM

Epicor905.dbo.Vendor;

the map option is better suited if you want to create a flag for them and then use this same flag in you front end to identify each type of store

[maxgro]

// test data, 5 company (1..5), 5 vendor by company (A..E)

company: load rowno() as company AutoGenerate 5;

vendor: load chr(ord('A') + rowno() -1) as vendor AutoGenerate 5;

join (company)

LOAD vendor, floor(rand()*100) as sales Resident vendor;

DROP Table vendor;

// replace  with inline load (56), list of excluded company-vendor

// I exclude 1A 1B 1C 2A...3C (9)

company_vendor_excluded:

load company & '-' & vendor as company_vendor_excluded

Resident company

where company<=3 and ord(vendor)-ord('A')+1 <=3;

// load not excluded company-vendor sales

company_sales_final:

NoConcatenate load *

Resident company

where not exists(company_vendor_excluded, company & '-' & vendor);

DROP Table company;

[#4]

I ran with the following...to see if it would eliminate just a single vendor.. and it unfortunately eliminated all entries from that data source.

Mapping
Excluded:
load*Inline
[Excluded,Value
CMI_008720,1
];


Load
Company &'_'& VendorNum As KEY_CompanyVendorNum,
Company,
Company &'_'& VendorID as "CompanyVendorID",
Name as "Vendor Name",
VendorID
    where not Exists(Excluded,Company &'_'& VendorID);

   
SQL SELECT
     Company,
     Name,
    VendorID,
    VendorNum

FROM
Epicor905.dbo.Vendor;

[ramoncova06]

my bad, get rid of the mapping on the inline

Excluded:
load*Inline
[Excluded,Value
CMI_008720,1
];


Load
Company &'_'& VendorNum As KEY_CompanyVendorNum,
Company,
Company &'_'& VendorID as "CompanyVendorID",
Name as "Vendor Name",
VendorID
    where not Exists(Excluded,Company &'_'& VendorID);

   
SQL SELECT
     Company,
     Name,
    VendorID,
    VendorNum

FROM
Epicor905.dbo.Vendor;

[maxgro]

I didn't use mapping load

PFA

[#7]

The last version worked perfectly.... thanks so much for your assistance...

[maxgro]

please mark the correct / helpful answers, Ramon's answer I suppose.

Thanks and good Qlik

[#9]

YES, Sorry,

Using the LOAD*INLINE to create the excluded list of values
Excluded:
load*Inline
[Excluded,Value
CMI_008720,1
];

With the    WHERE NOT EXISTS in the data load excluding the joined field unique identifier, which creates the matched values from the LOAD*INLINE list.

where not Exists(Excluded,Company &'_'& VendorID);

Eliminated those line items from being presented in my data set.