Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
salto
Specialist II
Specialist 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
5 Replies
shree909
Partner - Specialist II
Partner - Specialist II

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
Specialist II
Specialist II
Author

Thank you! It works like you proposed.

Regards.

Not applicable

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

hic
Former Employee
Former Employee

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