topic Re: Data reduction based on more than one field in QlikView

Data reduction based on more than one field

salto — Wed, 23 Jan 2013 17:44:54 GMT

Hello,

I am facing a problem regarding to the data reduction in QV documents.

Until now, we have made the reduction based on the value of the "SalesPerson" field. We have a hidden script like this:

Section Access;

Directory;

LOAD [ACCESS], NTNAME, [GROUP]

FROM UserList.xls (biff, embedded labels, table is Sheet1$);

Load * INLINE [

GROUP, OMIT

WORK, NAME

];

Section Application;

STAR IS *;

And we have a Data reduction like this:

Reductions:

LOAD NTNAME,

SalesPerson

FROM Reductions.xls (biff, embedded labels, table is Sheet1$);

So depending on the values of the SalesPerson filed in the Excel file, a NTNAME user can read the data of one or more SalesPerson depending on the values. In this example, the J.SMITH NT user can read John Smith's salesperson's records in the database. The same for M.GREENE (only Maurice Greene's records). And Usain Bolt can read his own records as well as the records for the two other salespeople. The same goes for ADMIN with the '*':

NTNAME	SalesPerson
J.SMITH	John Smith
M.GREENE	Maurice Greene
U.BOLT	John Smith
U.BOLT	Maurice Greene
U.BOLT	Usain Bolt
ADMIN	*

We are facing a limitation in this model, and we would like to add another reduction type: the COUNTRY. Let's say that C.LEWIS should be able to read all the records for all the salespeople, but only the records with the value ITALY for the Country. Our salespeople sell items all over the world!

The first thing I thought was to ad a new column to the Reductions table:

NTNAME	SalesPerson	Country
J.SMITH	John Smith	*
M.GREENE	Maurice Greene	*
U.BOLT	John Smith	*
U.BOLT	Maurice Greene	*
U.BOLT	Usain Bolt	*
ADMIN	*	*
C.LEWIS	*	Italy

And add a second line to the Reductions:

Reductions:

LOAD NTNAME,

SalesPerson,

Country

FROM Reductions.xls (biff, embedded labels, table is Sheet1$);

But after logging with C.LEWIS, this only shows me an empty set of values (it does not show any record!)

Is it possible to do this in QV? If so, how can this be achieved?

Many thanks in advance!

Re: Data reduction based on more than one field

hic — Thu, 24 Jan 2013 16:56:54 GMT

This is possible.

See http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

and http://community.qlik.com/docs/DOC-3451

HIC

Re: Data reduction based on more than one field

shree909 — Thu, 24 Jan 2013 17:02:46 GMT

Hi think for the second reduction use this table and find out if it works

Section Access;

Directory;

LOAD

[ACCESS],

NTNAME,

[GROUP]

FROM UserList.xls (biff, embedded labels, table is Sheet1$);

Load * INLINE [

GROUP, OMIT

WORK, NAME

];

Section Application;

STAR IS *;

And we have a Data reduction like this:

Reductions1:

LOAD NTNAME,

SalesPerson

FROM Reductions.xls (biff, embedded labels, table is Sheet1$);

Reductions2:

LOAD

SalesPerson,

Country

FROM Reductions.xls (biff, embedded labels, table is Sheet1$)

Re: Data reduction based on more than one field

salto — Mon, 28 Jan 2013 08:50:54 GMT

Thank you! It works like you proposed.

Regards.

Re: Data reduction based on more than one field

Mon, 28 Jan 2013 08:54:45 GMT

can anybody tell me the concept of resident table in qlikview i had browsed but i cant understand if u r having any tutorials means please share them

Re: Data reduction based on more than one field

hic — Tue, 29 Jan 2013 14:30:50 GMT

There really isn't very much to understand. A resident table is a table that has been loaded previously in the script by a Load or SELECT statement. So, you can load data from a resident table using

Load .... Resident <TableName>;

HIC