topic Restrict Data during loading process in QlikView

Restrict Data during loading process

hkg_qlik — Wed, 26 Jan 2011 03:05:49 GMT

Hi,

I have a Revenue Fact table I am loading from a Data Warehouse. But I want to restrict the data from loading like we do where clause in SQL.

Example: Table NAme: REVENUE_FACT

Field name/Column Name : 1.Revenue, 2.COGS, ORDER_TYPE etc

Now while loading the script I want to restrict the ORDER_TYPE not equal to Transfer Order.

How can I do that?

Thanks,

Restrict Data during loading process

rwunderlich — Wed, 26 Jan 2011 03:13:14 GMT

The best way to do this is to continue to do the filtering in the SQL statement.

SQL SELECT * FROM REVENUE_FACT
WHERE ORDER_TYPE <> 'Transfer Order';

-Rob

Restrict Data during loading process

hkg_qlik — Wed, 26 Jan 2011 03:21:49 GMT

Thanks Rob.

Also is there a way we can restrict it at a Report level?

Thanks,

Restrict Data during loading process

rwunderlich — Wed, 26 Jan 2011 04:19:10 GMT

By Report level, I'm assuming you mean the document level ('Report" is an object type in a QV document).

You can exclude data from a document by making selections. If you want to make the selections "hard", you can lock the selections and also use bookmarks to make the selections easy to apply. If you want to remove the data entirely for certain users (User Joe does not see any rows where OrderType="Transfer") then look at Section Access security in the reference guide.

-Rob

Restrict Data during loading process

rotero — Mon, 05 Dec 2011 07:28:37 GMT

is there a way to restrict inclusion of records from excel file data loading?

ex.

LOAD Cycle,

Team,

TerrCode as Terr,

Rep_Name,

MD_Name,

Specialty,

Location,

Class,

Target,

Actual

FROM

(biff, embedded labels, table is [Cycle 9 Call Report$])

this is the sample load statement but i need to restrict Terr<>'specific value'

Re: Restrict Data during loading process

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 08:12:28 GMT

Hi,

WHERE clause will work with excel files as well

Table:
LOAD Cycle,
     Team,
     TerrCode as Terr,
     Rep_Name,
     MD_Name,
     Specialty,
     Location,
     Class,
     Target,
     Actual
FROM
(biff, embedded labels, table is [Cycle 9 Call Report$]) 
WHERE Terr <> 'Something';

Hope that helps.

Miguel

Restrict Data during loading process

rotero — Mon, 05 Dec 2011 10:36:22 GMT

my first attempt is i use alias Terr rather TerrCode and it fail

then i tried the orig fieldname and its works..

BIG THANKS!!

Restrict Data during loading process

jagan — Mon, 05 Dec 2011 11:07:21 GMT

Hi,

Use the Limited Option in

Edit Script -> Debug -> Limit Load -> Select and give number of rows to be loaded.

Loads the given number of rows in all tables.

Regards,

jagan.

Re: Restrict Data during loading process

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 11:10:28 GMT

Hi Jagan,

Just to avoid any further confussion, that will limit the load regardless the value in a field, loading only the first n lines from the source, and that is not the case. If you only want to load records where Company = 'A', in either an excel file, a qvd file or any other data source, you need to do that using the WHERE clause, either in the source query (SQL for example) or in the LOAD part.

Regards.

Miguel

Re: Restrict Data during loading process

jagan — Mon, 05 Dec 2011 11:17:45 GMT

Hi,

You are correct Miguel.

Regards,

Jagan.