topic Re: Multiple cross value exclusion in QlikView

Multiple cross value exclusion

Tue, 05 May 2015 17:56:42 GMT

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.

Re: Multiple cross value exclusion

ramoncova06 — Tue, 05 May 2015 18:07:30 GMT

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 )

Re: Multiple cross value exclusion

marcus_sommer — Tue, 05 May 2015 18:15:36 GMT

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

Re: Multiple cross value exclusion

Tue, 05 May 2015 19:26:28 GMT

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;

Re: Multiple cross value exclusion

ramoncova06 — Tue, 05 May 2015 19:36:06 GMT

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;

Re: Multiple cross value exclusion

Tue, 05 May 2015 19:58:12 GMT

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,

Re: Multiple cross value exclusion

ramoncova06 — Tue, 05 May 2015 20:25:34 GMT

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

Re: Multiple cross value exclusion

Tue, 05 May 2015 20:31:00 GMT

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...

Re: Multiple cross value exclusion

ramoncova06 — Tue, 05 May 2015 20:40:34 GMT

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;

Re: Multiple cross value exclusion

Tue, 05 May 2015 20:56:47 GMT

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.

Re: Multiple cross value exclusion

ramoncova06 — Tue, 05 May 2015 21:10:57 GMT

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

Re: Multiple cross value exclusion

maxgro — Tue, 05 May 2015 21:25:54 GMT

// 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;

Re: Multiple cross value exclusion

Tue, 05 May 2015 21:33:38 GMT

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;

Re: Multiple cross value exclusion

ramoncova06 — Tue, 05 May 2015 21:43:23 GMT

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;

Re: Multiple cross value exclusion

maxgro — Tue, 05 May 2015 21:44:54 GMT

I didn't use mapping load

PFA

Re: Multiple cross value exclusion

Wed, 06 May 2015 15:33:42 GMT

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

Re: Multiple cross value exclusion

maxgro — Wed, 06 May 2015 17:43:40 GMT

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

Thanks and good Qlik

Re: Multiple cross value exclusion

Wed, 06 May 2015 17:52:18 GMT

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.