Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
// 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;
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;
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;
I didn't use mapping load
PFA
The last version worked perfectly.... thanks so much for your assistance...
please mark the correct / helpful answers, Ramon's answer I suppose.
Thanks and good Qlik
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.