Excluding multiple fields in script

[khaycock]

I have a large chunk of data within a field that I was hoping to exclude from the script but I was hoping i could bring in an Excel file instead with the data that needs to be excluded from the field rather than hard-coding them all in. Is this possible?

[marcus_sommer]

Yes, there are various ways possible to load data and to create from them parts or a whole load-statement on the fly and/or to filter further loads, for example:

t1: load concat(Fields) as Fields from Excel;

let vFields = peek('Fields', 0', 't1');

t2: load $(vFields) from Source;

- Marcus

[khaycock]

What would I do in the situation where the data is joined to a table? I tried to follow your lead but get confused at that part.

Excluded:
LOAD PART_NBR
FROM
[Excel]
(ooxml, embedded labels, table is [Excluded Parts]);

let vFields = peek('Orders','0','Excluded');

Left Join (Orders)
ModelMap:
LOAD PART_NBR,
MODEL_PN
FROM
[Excel]
(ooxml, embedded labels, table is [Model Map]);

[marcus_sommer · Accepted solution]

My example was related to your posting-title for excluding fields from a load-statement and not to exclude field-values. In regard to your example the following will exclude "PART_NBR" from the join which will remove all records from Orders which aren't included within the join-load:

Excluded:
LOAD PART_NBR
FROM
[Excel]
(ooxml, embedded labels, table is [Excluded Parts]);

INNER Join (Orders)
ModelMap:
LOAD PART_NBR,
MODEL_PN
FROM
[Excel]
(ooxml, embedded labels, table is [Model Map]) where not exists(PART_NBR);

If Orders shouldn't be filtered else one of the join-fields should be added then keep by left and not inner join.

Update: depending on the previous loaded fieldvalues my suggestion might need to be adjusted to the following:

Excluded:
LOAD PART_NBR as PART_NBR_ForExists
FROM
[Excel]
(ooxml, embedded labels, table is [Excluded Parts]);

INNER Join (Orders)
ModelMap:
LOAD PART_NBR,
MODEL_PN
FROM
[Excel]
(ooxml, embedded labels, table is [Model Map]) where not exists(PART_NBR_ForExists, PART_NBR);

- Marcus

[khaycock]

There are 4 'unmapped' PART_NBR fields:

In my excluded parts sheet, I have the top 3 listed. 

When I do the second option, it completely removes all of the 4 part numbers rather than just the 3 written in the sheet. Is this to do with the join?

[marcus_sommer]

Please provide your script with a few sample records, something in this way:

Orders:
load * inline [
PART_NBR, OtherField
a, 1
b, 2
];

for all these tables with a few of the matching and unmatching fieldvalues and which results you get and which ones you would expect.

- Marcus

[khaycock]

Orders:
load * inline [
INVENTORY_ITEM_ID &'|'& SHIP_FROM_ORG_ID as PartStoreKey, ORDER_NUMBER
11970496|23, 2000019787
5973441|23, 2000019279
];

Items:
load * inline [
INVENTORY_ITEM_ID &'|'& ORGANIZATION_ID as PartStoreKey, PART_NBR
11970496|23, 654598G01-PB
5973441|23, 062854
];

Left Join (Orders)
ModelMap:
load * inline [
PART_NBR, VALUE_STREAM
062854, GOLF
654598G01-PB, GOLF
];

And then I wanted to exclude some specific PART_NBR data from another sheet but not sure how to include in the script or even in the Excel sheet as all I have is a list of part numbers that will be added into a singular column somewhere

[marcus_sommer]

It's not really clear for me - should the table "Items" contain the excluding values and if why is there the field "PartStoreKey"? Further the fieldvalues of "PART_NBR" are only the identically ones to the table "ModelMap" (if we would filter them against each other there would be no record left) and "ModelMap" has no KEY field for the table "Orders". Therefore what shoud really happens?

- Marcus

[khaycock]

Haha your guess is as good as mine! I was just hoping to have a separate list of part numbers to exclude rather than listing them all indivdually as a WHERE under the Items load. Do you think I should just hard code them instead then? 

[marcus_sommer]

No, I don't think you should hardcode them. My above suggestion with exists() will work. Maybe you need to dive a bit deeper into the logic of it to be able to apply it. For this take a look in the help and to the various links at the bottom of the following posting which explain the functionalities quite well: Advanced-topics-for-creating-a-qlik-datamodel.

- Marcus