topic Re: Preventing load based on field values? in QlikView

Preventing load based on field values?

Thu, 09 Oct 2014 17:46:36 GMT

I would like to add a measure of error-prevention to our application to prevent improper data from being loaded, and I was wondering if it was possible to prevent data from loading (or conditionally load) based on the values contained within. I.E let's say I have the following data which I would like to load from Data.xls...

Security Check	Employee	Salary
ACCOUNTING	Bob Smith	70000
ACCOUNTING	Ashkat Ayer	75000
ACCOUNTING	Cecilia Moralez	120000
MANAGEMENT	Dave Brown	15000
RESEARCH	Tan Xi	65000
ACCOUNTING	Larry Marks	35000

But, for whatever reason, I want to make sure that ONLY lines with "ACCOUNTING" in "Security Check" are loaded, or - even better - that the file will not load UNLESS "Security Check" is exclusively filled with "Accounting." Is this possible?

Re: Preventing load based on field values?

Thu, 09 Oct 2014 18:32:28 GMT

You can add a WHERE clause to your load statement. Such as:

LOAD *

FROM

[<your file>]

Where [Security Check] = 'ACCOUNTING';

Re: Preventing load based on field values?

maxgro — Thu, 09 Oct 2014 19:13:45 GMT

you can check the records are always ACCOUNTING in this way

LOAD [Security Check], Employee, Salary

FROM [http://community.qlik.com/thread/137186] (html, codepage is 1252, embedded labels, table is @1)

;

NoConcatenate load * Resident a where [Security Check] = 'ACCOUNTING';

if NoOfRows('a') <> NoOfRows('b') then

trace there are records not ACCOUNTING;

ENDIF;

drop table a;

Re: Preventing load based on field values?

antoniotiman — Thu, 09 Oct 2014 19:55:50 GMT

If You don't want to load Table UNLESS ....

Load

count(*) as Count1

from Table

where [Security Check] = 'ACCOUNTING';

Let vCount1 = Peek('Count1');

Load

count(*) as Count2

from Table;

Let vCount2 = Peek('Count2');

If vCount1=vCount2 then

Load * from Table;

ENDIF;

Re: Preventing load based on field values?

Wed, 15 Oct 2014 20:35:09 GMT

Thanks all - ended up solving this in a different way on the back end.