Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

salto
Valued Contributor II

Data reduction based on more than one field

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.SMITHJohn Smith
M.GREENEMaurice Greene
U.BOLTJohn Smith
U.BOLTMaurice Greene
U.BOLTUsain 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 SalesPersonCountry
J.SMITHJohn Smith*
M.GREENEMaurice Greene*
U.BOLTJohn Smith*
U.BOLTMaurice Greene*
U.BOLTUsain 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!

1 Solution

Accepted Solutions

Re: Data reduction based on more than one field

5 Replies

Re: Data reduction based on more than one field

shree909
Valued Contributor II

Re: Data reduction based on more than one field

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

salto
Valued Contributor II

Re: Data reduction based on more than one field

Thank you! It works like you proposed.

Regards.

Not applicable

Re: Data reduction based on more than one field

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

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