Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
khaycock
Contributor

Excluding multiple fields in script

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?

Labels (1)
1 Solution

Accepted Solutions

Re: Excluding multiple fields in script

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

9 Replies

Re: Excluding multiple fields in script

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
Contributor

Re: Excluding multiple fields in script

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]);

 

Re: Excluding multiple fields in script

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
Contributor

Re: Excluding multiple fields in script

There are 4 'unmapped' PART_NBR fields:

part num.PNG

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?

Re: Excluding multiple fields in script

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
Contributor

Re: Excluding multiple fields in script

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

 

 

 

 

Re: Excluding multiple fields in script

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
Contributor

Re: Excluding multiple fields in script

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? 

Highlighted

Re: Excluding multiple fields in script

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

Community Browser