topic Creating login and reducing(filtering) data in QlikView

Creating login and reducing(filtering) data

Fri, 27 Nov 2009 14:15:46 GMT

I will be really grateful if there is anyone who could (and is willing to) help me.

Items and Sales are the two tables which i have loaded in to qlikview. Now i wanted to create login(userid and password available in items table) and reduce the data according to account(i.c_account_code as account) and mfaccode(i.c_mfac_code as mfaccode).

First let know how i can create login with username and password to be entered by the user. Secondly how to reduce(filter) the data according to account and mfaccode.

Below is the structure of table which i have loaded into the application. Hope some one can help me out.

items:
SQL select i.c_account_code as account,i.c_item_code as itemcode,c_pack as pack,
i.c_mfac_code as mfaccode,rtrim(i.c_name)+' '+rtrim(i.c_pack) as itemname,
rtrim(i.c_account_code)+rtrim(i.c_mfac_code) as userid,rtrim(m.c_password) as password,
i.n_mrp as MRP,i.n_newflag as recentitem,i.c_Scheme as schemes,m.c_name as mfacname,
c.c_name as contentname,c.c_note as contentname1
from item_mst i inner join mfac_mst m on i.c_mfac_code=m.c_mfac_code
left outer join content_mst c on i.c_content_code=c.c_content_code;

sales:
SQL select stk_sales.c_ac_code as account,Year(d_date) as Year,Month(d_date) as Month,stk_sales.c_item_code as itemcode,
n_purqty as PurQty,n_pur_Sch_qty as PurSch,n_purvalue as PurValue,
n_salqty as SalQty,n_sal_sch_qty as SalSch,n_salvalue as SalValue,
n_crqty as RtnQty,n_cr_sch_qty as RtnSch,n_crvalue as RtnValue,
n_clqty as StkQty,n_clvalue as StkValue from stk_sales,item_mst, mfac_mst
where stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code;

Creating login and reducing(filtering) data

prieper — Fri, 27 Nov 2009 17:26:37 GMT

Please check the handbook for restriction of access, basically you should have a script like

SECTION Access;

 LOAD * INLINE [
 ACCESS, USERID, PASSWORD
 ADMIN, ADMIN, PW0
 USER, Mike, PW1
 USER, Joe, PW2];
SECTION Application;

 STAR IS *;
 LOAD * INLINE [USERID, account
 ADMIN, *
 Mike, 0001
 Mike, 0002
 Joe, 0001
 Joe, 0002
 Joe, 0003];

This would give ADMIN right to see all accounts, Mike can see account 1+2, Joe 1-3. (Might make sense to probably group the accounts).
You also need to check for Document Properties -> Opening -> Initial Data Reduction ...

HTH
Peter

Creating login and reducing(filtering) data

Sat, 28 Nov 2009 13:29:25 GMT

Thanks for the reply!

Now i have created the login. But the problem now is if I load the data using the Inline data wizard. It loads only the distinct values. Problem is for few userid the password is same. As it loads only the distinct values it has loaded the password for the first record and rest of the record are blank. Due to this rest of users(records) when they enter the userid it doesn't ask for the password field. I mean it is allowing those user to enter without password being entered.

Give some solution for this problem.

Once i get the solution i need to reduce the data according the account and mfaccode. Help for this also.

Creating login and reducing(filtering) data

Mon, 30 Nov 2009 19:06:46 GMT

Hi Peter Rieper & Vittaly!

Can you please write the load script according to the table structure which is attached with my question.