Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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$)
Thank you! It works like you proposed.
Regards.
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
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