Exclude records from LOAD

Thu, 14 Jan 2016 10:15:43 GMT

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?

Re: Exclude records from LOAD

sunny_talwar — Thu, 14 Jan 2016 19:18:02 GMT

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

Re: Exclude records from LOAD

maxgro — Thu, 14 Jan 2016 19:20:30 GMT

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')

(Customer = 'Customer789' and Year = '2004' and Agent = 'AgentABC')

);

Re: Exclude records from LOAD

Thu, 14 Jan 2016 19:21:48 GMT

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'

Load

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.

as is used for assigning a new name to the field.

Re: Exclude records from LOAD

alexpanjhc — Thu, 14 Jan 2016 19:24:17 GMT

load *

from abc.file

where

not (Customer = 'Customer123' and Year = '2005' and Agent = 'Agent456') and

not (Customer = 'Customer789' and Year = '2004' and Agent = 'AgentABC')

topic Re: Exclude records from LOAD in QlikView

Exclude records from LOAD

Re: Exclude records from LOAD

Re: Exclude records from LOAD

Re: Exclude records from LOAD

Load

Re: Exclude records from LOAD