topic Issue with loading data from one table and dropping the same table from where data has been loaded in QlikView

Issue with loading data from one table and dropping the same table from where data has been loaded

Fri, 26 Mar 2010 12:03:40 GMT

Hi All,

I am retrieving the admin login from from different table(mfac_mst) which i want to insert into the below table and drop the table(mfac_mst) to avoid the synthetic key. Let me know how to do that. If I follow the below method though the table has been dropped synthetic key has been formed. Some one let me know how to over come this problem.


TempTab:
LOAD DISTINCT
 account,
 mfaccode,
 [userid],
 [password]
resident items;

// Loading admin login from mfac_mst

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];

Tab1:

select * from mfac_mst;

select rtrim(c_account_code)+rtrim(c_loginid) as userid,rtrim(c_password) as password,c_account_code as SecurityAccount,
'*' as SecurityMfa from mfac_mst where c_loginid in('vadmin','admin');

drop table Tab1;

//////////////////////////////////////////////

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Fri, 26 Mar 2010 14:21:56 GMT

concatenate() the tables together. Or create two tables, load the fields you need into a resident table and drop the two tables drop tables tablename;

Issue with loading data from one table and dropping the same table from where data has been loaded

Sat, 27 Mar 2010 08:30:07 GMT

Hi Anthony!

Can you please do the required changes in the attachment. As I didn't get you. Please help me out!

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Sat, 27 Mar 2010 12:32:44 GMT

Hi Anthoy!

I have concatenated both the table to get the admin login. But how can i differentiate the admin login in the TempTab. If all the field are same in the temptab both the user and admin are behaving in the same way. Let me know what can be done now.


items:

LOAD *,
account as %Key;
SQL select i.c_account_code as account/*as accountas %key*/,i.c_item_code as itemcode,c_pack as pack,upper(i.c_account_code)

as SecurityAccount,upper(i.c_mfac_code) as SecurityMfa,
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;

TempTab:
LOAD DISTINCT
 account,
 mfaccode,
 [userid],
 [password]
resident items;

items:

concatenate select c_account_code as account,upper(c_account_code)as SecurityAccount,
upper(c_mfac_code) as SecurityMfa,c_mfac_code as mfaccode,rtrim(c_account_code)+rtrim(c_loginid) as userid,
rtrim(c_password) as password,c_name as mfacname from mfac_mst where c_loginid in('vadmin','admin');

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Oleg_Troyansky — Sat, 27 Mar 2010 17:50:54 GMT

If you prefer to have the two tables separated, - it's OK, you don't have to concatenate. Just name the tables separately and at the end, drop both tables.

Issue with loading data from one table and dropping the same table from where data has been loaded

Sun, 28 Mar 2010 07:20:57 GMT

Hello Oleg!

After naming the table separately and the dropping the same there has been some issues as shown below. Please let me know why it is happening. Is there is any other solution.

I don't think we can drop the items table as it will be required for me to get the sales figure.


CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];
items:

LOAD *,
account as %Key;
SQL select i.c_account_code as account/*as accountas %key*/,i.c_item_code as itemcode,c_pack as pack,upper(i.c_account_code)

as SecurityAccount,upper(i.c_mfac_code) as SecurityMfa,
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,
//rtrim(i.c_account_code)+rtrim(m.c_loginid) 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;
//where left(i.c_name,1)<>'~' and left(m.c_name,1)<>'~';

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];

items1:

select c_account_code as account,upper(c_account_code)as SecurityAccount,
upper(c_mfac_code) as SecurityMfa,c_mfac_code as mfaccode,rtrim(c_account_code)+rtrim(c_loginid) as userid,
rtrim(c_password) as password,c_name as mfacname from mfac_mst where c_loginid in('vadmin','admin');
drop table items;

drop table items1;

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Sun, 28 Mar 2010 08:24:09 GMT

Hi All,

Let me tell you the purpose of doing this.


// From this table I am able to get only the user login.
items:

LOAD *,
account as %Key;
SQL select i.c_account_code as account/*as accountas %key*/,i.c_item_code as itemcode,c_pack as pack,upper(i.c_account_code)

as SecurityAccount,upper(i.c_mfac_code) as SecurityMfa,
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,
//rtrim(i.c_account_code)+rtrim(m.c_loginid) 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;
//where left(i.c_name,1)<>'~' and left(m.c_name,1)<>'~';
// From here i am concatenating this table to get the admin login. If iam loading as a separate table synthetic key is formed.
CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];

items:

concatenate select c_account_code as account,upper(c_account_code)as SecurityAccount,
upper(c_mfac_code) as SecurityMfa,c_mfac_code as mfaccode,rtrim(c_account_code)+rtrim(c_loginid) as userid,
rtrim(c_password) as password,c_name as mfacname from mfac_mst where c_loginid in('vadmin','admin');

Now after concateting I am able to get the admin login with the user login. But let me know how can i differentiate the admin and user in the section access.

For more details please see the attached script.

Issue with loading data from one table and dropping the same table from where data has been loaded

Oleg_Troyansky — Sun, 28 Mar 2010 15:11:04 GMT

I'm not sure I understand what you mean by "differentiating user and admin" - usually the difference is manifested in the value of the field "ACCESS"...

Looking at your code, I spotted a few problems:

Section Access has to be in the very beginning of the document, there should be no other code before it.
All the fields in section access, including your additional fields, need to be in upper case (if I remember correctly)

Issue with loading data from one table and dropping the same table from where data has been loaded

Mon, 29 Mar 2010 07:15:15 GMT

Hello Oleg!

Happy Morning! Hope you are doing good.

I'm not sure I understand what you mean by "differentiating user and admin" - usually the difference is manifested in the value of the field "ACCESS"... -->


Section Access;
// Using this I am giving access to the 'USER'. But if it is in the same table and column how will i give access to user and admin. What i mean to say is i am getting the userid for both admin/user from the same column and not like the one which was given below upper(account). Hope you can understand my problem now.

SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(account) as SecurityAccount,
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab;
// Here I am giving access to the 'Admin'. Which i can't use anymore as i need to consider upper(userid) not upper(account).
CONCATENATE (SecurityTable)
LOAD DISTINCT
 'ADMIN' as [ACCESS],
 upper(account) as [USERID],
 upper(account) as [PASSWORD],
 upper(account) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab;

Section Access has to be in the very beginning of the document, there should be no other code before it. --> ? Surely I will keep the section access in the beginning.
All the fields in section access, including your additional fields, need to be in upper case (if I remember correctly) --> Ok I will check the same.

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Mon, 29 Mar 2010 10:36:39 GMT

Hi Oleg,

Section Access has to be in the very beginning of the document, there should be no other code before it. --> I have placed it in the beginning but I didn't worked

Have attached the script. Can you please place it in order.

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Wed, 31 Mar 2010 11:59:08 GMT

Hello Oleg!

Any update? I am waiting for your reply!

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Tue, 06 Apr 2010 12:39:46 GMT

Hello Oleg

Please provide some solution for me. It's been very long since I am waiting for your solution.

Thanks and Regards,

Rikab

Issue with loading data from one table and dropping the same table from where data has been loaded

Wed, 07 Apr 2010 08:25:43 GMT

Hello Oleg!

Did you find any solution for this issue. Please help me out when you get free time.

Thanks and Regards,

Rikab