Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to exclude multiple records from a LOAD, for example:
Customer = 'Customer123' and Year = '2005' and Agent = 'Agent456'
Customer = 'Customer789' and Year = '2004' and Agent = 'AgentABC'
and so on
How can i achive this in a LOAD?
maybe
load * inline [
Customer, Year, Agent
Customer123,2005,Agent456
Customer789,2004,AgentABC
Customer123,2005,a
Customer123,2005,b
c,2004,AgentABC
]
where
not
(
(Customer = 'Customer123' and Year = '2005' and Agent = 'Agent456')
or
(Customer = 'Customer789' and Year = '2004' and Agent = 'AgentABC')
);
May be this:
LOAD *
FROM XYZ
Where not ((Customer = 'Customer123' and Year = 2005 and Agent = 'Ageny456') or
(Customer = 'Customer789' and Year = 2004 and Agent = 'AgenyABC'));
maybe
load * inline [
Customer, Year, Agent
Customer123,2005,Agent456
Customer789,2004,AgentABC
Customer123,2005,a
Customer123,2005,b
c,2004,AgentABC
]
where
not
(
(Customer = 'Customer123' and Year = '2005' and Agent = 'Agent456')
or
(Customer = 'Customer789' and Year = '2004' and Agent = 'AgentABC')
);
as Sunny said, you would want to use the 'where' clause - the where clause is sued to limit the load based on where data equals or does not equal specified values
There is good information in the QlikView help if you searh on 'where'
The load statement loads fields from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent select statement or by generating data automatically. The general syntax of the load statement is:
load [ distinct ] *fieldlist
[( from file [ format-spec ] |
from_field fieldassource [format-spec]
inline data [ format-spec ] |
resident table-label |
autogenerate size )]
[ where criterion | while criterion ]
[ group_by groupbyfieldlist ]
[order_by orderbyfieldlist ]
where:
distinct is a predicate used if only the first of duplicate records should be loaded.
*fieldlist ::= ( * | field { , field } )
A list of the fields to be loaded. Using * as field list indicates all fields in the table.
field ::= ( fieldref | expression ) [ as aliasname ]
The field definition must always contain a literal, a reference to an existing field, or an expression.
fieldref ::= ( fieldname |@fieldnumber |@startpos:endpos [ I | U | R| B ] )
fieldname is a text that is identical to a field name in the table. Note that the field name must be enclosed by straight double quotation marks or square brackets if it contains e.g. spaces. Sometimes field names are not explicitly available. Then a different notation is used:
@fieldnumber represents the field number in a delimited table file. It must be a positive integer preceded by "@". The numbering is always made from 1 and up to the number of fields.
@startpos:endpos represents the start and end positions of a field in a file with fixed length records. The positions must both be positive integers. The two numbers must be preceded by "@" and separated by a colon. The numbering is always made from 1 and up to the number of positions. If @startpos:endpos is immediately followed by the characters I or U, the bytes read will be interpreted as a binary signed (I) or unsigned (U) integer (Intel byte order). The number of positions read must be 1, 2 or 4. If @startpos:endpos is immediately followed by the character R, the bytes read will be interpreted as a binary real number (IEEE 32-bit or 64 bit floating point). The number of positions read must be 4 or 8. If @startpos:endpos is immediately followed by the character B, the bytes read will be interpreted as a BCD (Binary Coded Decimal) numbers according to the COMP-3 standard. Any number of bytes may be specified.
expression can be a numeric function or a string function based on one or several other fields in the same table. For further information, see the syntax of Script Expressions.
load *
from abc.file
where
not (Customer = 'Customer123' and Year = '2005' and Agent = 'Agent456') and
not (Customer = 'Customer789' and Year = '2004' and Agent = 'AgentABC')