28 Replies Latest reply: Apr 6, 2010 8:44 AM by Dilip Jain RSS

    Problem in loading the admin login of my document! Help needed very urgently!

      Hi All,

      I am not able to get any solution even after referring to all the available document with me. I know everything will not be there straight forward in the document. But though i posted this problem many times in the forum i did not managed to get the solution for my problem. So thought to put the new post in little different way for better understanding for the experts who can give solution for this problem.

      Using below sql i used to get the USER's userid and password from the items table.

       


      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,
      --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;


      But for getting the ADMIN's userid and password is giving me trouble. To get the details i need to connect to the mfac_mst table. But due to the synthetic key problem i did not connect that. Let me know how can i get the ADMIN's user id and password from the current structure. Do i need to use the inline function to get the admin details ??? If yes let me know how to use it here to get the same and give the admin rights for the document.

      I have also don't know how to define the USER and ADMIN user of my document. In my current structure i don't know how to define the same.

      If i load the admin details using the below code it creates one more synthetic key.

       

      select c_mfac_code as mfaccode,c_account_code as account,rtrim(c_account_code)+rtrim(c_mfac_code) as userid,c_password as password from mfac_mst where c_mfac_code in ('01','admin')


      So i am confused what to here! I want to have the admin login very urgently in my document. For example when admin logins in my document he/she should be in a position to see all the details related to the logged in admin's account. For eg if if admin logs in with the account code 001001 he should be in a position to see the data related to 001001 and if the admin logs in with account code 001007 he should be in position to see the data related to 001007.

      I have also attached the document which i am working on. Hope some one can help me out soon. It is very very urgent!

      To open the document you can use userid:0010012021, password:c2info99.

      Expecting some quick reply from the experts!

      Thanks and Regards,

      Rikab

       

       

       

        • Problem in loading the admin login of my document! Help needed very urgently!

          Hi All,

          I don't know whats wrong with this post. I am not getting much reply which and all are related to this post. I don't know why! Is it not understandable or missing any information. But i seriously in need of solution for this problem. Hope members of this community will consider this seriously and provide solution for this problem at the earliest.

          Thanks and Regards,

          Rikab

           

            • Problem in loading the admin login of my document! Help needed very urgently!
              Rakesh Mehta

              Try:


              items:
              LOAD *, account & '/' & itemcode as %Key;
              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;
              concatenate(items)
              SQL select
              c_mfac_code as mfaccode,
              c_account_code as account,
              rtrim(c_account_code)+rtrim(c_mfac_code) as userid,
              c_password as password
              from mfac_mst
              where c_mfac_code in ('01','admin');
              sales:
              SQL select
              stk_sales.c_ac_code & '/' & stk_sales.c_item_code as %Key,
              Year(d_date) as Year,
              Month(d_date) as Month,
              d_date as Date,
              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;
              Section Access;
              SecurityTable:
              LOAD
              'USER' as [ACCESS],
              userid as USERID,
              upper(password) as PASSWORD,
              account as [account code],
              mfaccode as [mfaccode]
              RESIDENT items;
              Section Application;


                • Problem in loading the admin login of my document! Help needed very urgently!

                  Hi Rakesh!

                  Thanks for taking up the challenge. I will surely check it and get back to you at the earliest.

                  Thanks and Regards,

                  Rikab

                   

                    • Problem in loading the admin login of my document! Help needed very urgently!

                      Hi Rakesh!

                      Did you found out why we get this error and also look like to what changes you did in my script and why it is required. Please reply me whenever you are free.

                      Thanks and Regards,

                      Rikab

                       

                        • Problem in loading the admin login of my document! Help needed very urgently!
                          Rakesh Mehta

                          Try this:

                           


                          sales:
                          LOAD *,
                          ac_code, & '/' & it_code as %Key;
                          SQL select
                          stk_sales.c_ac_code as ac_code,
                          stk_sales.c_item_code as it_code,
                          Year(d_date) as Year,
                          Month(d_date) as Month,
                          d_date as Date,
                          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;
                          drop fields ac_code, it_code;


                            • Problem in loading the admin login of my document! Help needed very urgently!

                              Hi Rakesh!

                              Thanks for your help! I am getting the following error when i tried with your script.

                              Script which has been used:

                               

                              <pre>items:
                              LOAD *, account & '/' & itemcode as %Key;
                              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;
                              concatenate(items)
                              SQL select
                              c_mfac_code as mfaccode,
                              c_account_code as account,
                              rtrim(c_account_code)+rtrim(c_mfac_code) as userid,
                              c_password as password
                              from mfac_mst
                              where c_mfac_code in ('01','admin');sales:
                              LOAD *,
                              ac_code, & '/' & it_code as %Key;
                              SQL select
                              stk_sales.c_ac_code as ac_code,
                              stk_sales.c_item_code as it_code,
                              Year(d_date) as Year,
                              Month(d_date) as Month,
                              d_date as Date,
                              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;drop fields ac_code, it_code;

                               



                                • Problem in loading the admin login of my document! Help needed very urgently!

                                  Hi Rakesh!

                                  Have you found out any why this error was thrown?

                                  Regards,

                                  Rikab

                                    • Problem in loading the admin login of my document! Help needed very urgently!
                                      Rakesh Mehta

                                      Rikab,

                                      I am not yet 24x7 on cummunity support. I usually logoff about 10PM EST, otherwise my wife thinks I am getting addicted to QlikCommunity. But it's too late now not to be, I guess. ;-)

                                      For your probelm take out the comma after ac_code.

                                      sales:
                                      LOAD *,
                                      ac_code, & '/' & it_code as %Key;

                                      Hope this helps.

                                        • Problem in loading the admin login of my document! Help needed very urgently!

                                          Hello Rakesh!

                                          I didn't mean you to be 24x7 on community support. I have seen your post and it was really worth. By the i am a newbie and thats the reason i always look for some to reply me.

                                          By the way i am at home now after reaching office i will the requested changes and will get back to you.

                                          Thanks and Regards,

                                          Rikab

                                           

                                          • Problem in loading the admin login of my document! Help needed very urgently!

                                            Hi Rakesh!

                                            You have provided me the solution to get the admin login. But to be frank as i am newbie. I am not too sure how the script provided by works.

                                            I think you are concatenating the common columns and naming it as the key field between the two tables. I am not sure how sales1 has been formed automatically. With this script you have managed to get the admin user's login. But let me know how to define the ADMIN and USER in the section access.

                                            Also let me know why the below script has been written.

                                             

                                            items:
                                            LOAD *, account & '/' & itemcode as %Key;
                                            sales:
                                            LOAD *,
                                            ac_code & '/' & it_code as %Key;


                                            Though i have given explanation for few things. I request you to give the brief explanation for the solution provided by you.

                                            Also requesting you to answer for the below query!

                                            Wont the below one will not create synthetic key issue? I am seeing lots of synthetic has been formed after adding your script. Will it not create performance issue? (I want to study about the synthetic key issue first. Please send me the document if you have any)

                                            Sorry from my side for asking so many things to you. It is not that i am not trying anything from my side. I am also trying from my side. Whatever i didn't understood i am putting it here.

                                            Expecting your reply!

                                            Happy weekend!

                                            Thanks and Regards,

                                            Rikab

                                              • Problem in loading the admin login of my document! Help needed very urgently!
                                                Rakesh Mehta

                                                Hi Rikab,

                                                It's very hard to give you 100% solution without knowling complete data model and data contents. Is there anyway you can upload your QVW file with some sample data in a file? Someone here, then, will provide you an example reading from that sample file, all you would need to do is then read from database and not file when you have it. Just keep the column names same in the data file.

                                                Also explain when is the relation between Sales and Items tables. Are they typical Sales header and items table?

                                                Rakesh

                                                  • Problem in loading the admin login of my document! Help needed very urgently!

                                                    Hi Rakesh!

                                                    Happy Morning!

                                                    It's very hard to give you 100% solution without knowling complete data model and data contents. --> Provided you the data contents in data contents sheets of the attached document.

                                                    Is there anyway you can upload your QVW file with some sample data in a file? Someone here, then, will provide you an example reading from that sample file, all you would need to do is then read from database and not file when you have it. Just keep the column names same in the data file. --> Sample data file has been provided to you in QVD format. It is available in the document folder.

                                                    Also explain when is the relation between Sales and Items tables. Are they typical Sales header and items table? --> Yes, they are typical sales and items table.

                                                    To open the application use userid: 0010011161, password: 7426

                                                    Thanks and Regards,

                                                    Rikab

                                    • Problem in loading the admin login of my document! Help needed very urgently!

                                      Hi Rakesh!

                                      Thanks for your help and sorry for my late response. I have applied the same script that was given by you in mine but it throws following error. Don't have any idea why this error has been thrown. Please check and help me out!

                                      Thanks and Regards,

                                      Rikab

                                  • Problem in loading the admin login of my document! Help needed very urgently!
                                    Rakesh Mehta

                                    This is how I would do it very quickly. Hope this gives you enough idea on how to continue.

                                      • Problem in loading the admin login of my document! Help needed very urgently!

                                        Hi Rakesh!

                                        Looks like you have almost finished it. But still i want to inform few things to you.

                                        The document which i am working on is for pharma distributors. The actors here are the DISTRIBUTOR(They have unique account(Distributor1:001001,Distributor2: 001007) code and there are N no of mfac(representative) comes under them) and COMPANY representative(N no of mfac(MFACCODE: N no of mfacode comes under the account code)

                                        So when i logs in with 0010012021(account:001001,mfacode:2021). It will show the data of mfaccode:2021 which belongs to the account:001001). if logs in with 0010072021(account:001007,mfacode:2021). It will show the data of mfaccode:2021 which belongs to the account:001001). For example when admin of (001001) logs in it should show the performance of all the manufactures and where as when the company rep with accountcode:001001 and mfacode:2021 they should be in a postion to see the item wise performance. Same like what is happening now.

                                        So i user with the account code 001001 will have one admin and the user with the account code 001007 will have one admin. To see the performance of both together there will be one more admin who can see both the account data(001001+00107).

                                        As per your logic you are concatenating the upper(account) & '/' & upper(mfaccode) as SecurityLink, in this case if i think i can see the over all data but if i want to login with admin with account(001001) i will not be able to see the data of the account:001001.

                                        Do i need to load the data every time from QVD? Is it possible for you to load it using the select query instead of LOAD. If this is not possible do i need to create separate document in the same folder to load the data in to the QVD and so that same will be loaded like what you are doing it here.

                                         


                                        LOAD
                                        'USER' as [ACCESS], upper([userid]) as [USERID], upper([password]) as [PASSWORD],
                                        upper(account) & '/' & upper(mfaccode) as SecurityLink // I used this as a filter. But you have concatenated it. Because of this admin of account(001001) will be able to see the over all data which comes under the account: 001001)
                                        resident TempTab;

                                        concatenate(SecurityTable)
                                        LOAD * INLINE [
                                        ACCESS, USERID, PASSWORD, SecurityLink
                                        ADMIN, ADMIN, ADMIN, *


                                        I am trying to learn your logic and method. I am really thankful for all your effort.Many thanks to you!

                                        When find time please do reply!

                                        Thanks and Regards,

                                        Rikab

                                         

                                        • Problem in loading the admin login of my document! Help needed very urgently!

                                          Hello Rakesh!

                                          Still i did not get any solution which i was looking for. Requesting you to work on it as i couldn't over come this issue from such a long time.

                                          Waiting for your reply!

                                          Thanks and Regards,

                                          Rikab

                                           

                                            • Problem in loading the admin login of my document! Help needed very urgently!

                                              Hello Rakesh!

                                              I am waiting for your reply! Please help me out.

                                              Thanks and Regards,

                                              Rikab

                                               

                                                • Problem in loading the admin login of my document! Help needed very urgently!
                                                  Rakesh Mehta

                                                  This should give you enough idea. Not sure why it's taking you too long to figure this out, may be you need to hire some help.

                                                  Anyway, try enclosed with following:

                                                  user: 0010011171 password:7636 --> Only access to 001001 account and 1171 mfacode

                                                  user: 001001 password: 001001 --> all mfacodes of Account 001001

                                                  user: admin password: admin --> full document code access

                                                    • Problem in loading the admin login of my document! Help needed very urgently!

                                                      Hi

                                                      This should give you enough idea. Not sure why it's taking you too long to figure this out, may be you need to hire some help. --> I agree with your point. I did took long time! May be you are right1 I may need to hire some help. As I am the only person 4m my organization working on this. So some times few simple thing also looks tougher for me. I also go through the help document but as expected no one will give straight forward answer in the document. We need to work on it. That's the reason to so much of time.

                                                      Sorry If I had taken so much time of yours. I will check the document which you have sent and will get back to you if any doubts are there.

                                                      Really Really sorry for taking so much time of yours.

                                                      I really appreciate your help that you gave these many days!

                                                      Thanks and Regards,

                                                      Rikab

                                                       

                                                       

                                                      • Problem in loading the admin login of my document! Help needed very urgently!

                                                        Hi Rakesh!

                                                        Really sorry to trouble you again. I know it may be simple thing but I don't know why I am not getting that grip in Section Access login. I tried the same logic that has been used by you in yours but it doesn't seems to be working in mine when I load the data directly from data source instead of qvd like yours. The problem is it is not taking the %key as it is showing some error message.

                                                        Can you do the same changes as per my load script which is attached with this post then in that case I feel it may be easy for me to understand.

                                                        Thanks in advance!

                                                        Regards,

                                                        Rikab

                                                         

                                                        • Problem in loading the admin login of my document! Help needed very urgently!

                                                          Hi Rakesh!

                                                          Sorry to trouble you again. When I use the same logic that you have used in yours while loading the data directly from the data source instead of qvd files it showing some error message. Problem is that while loading directly it is not allowing %key while loading. So can you please do the required changes in the script attached by me then i feel it will be easy for me to understand what changes you have did. I know it may be bit silly but i don't know why i am not able to get enough grip in section access part.

                                                          Sorry again to trouble you again. Hope you will do the required changes in mine so that i can understand the logic and apply the same without your help.

                                                          Thanks in advance,

                                                          Regards,

                                                          Rikab

                                                           

                                                          • Problem in loading the admin login of my document! Help needed very urgently!

                                                            Hi Rakseh Mehta!

                                                            I have some doubt in the following load script of yours. Please clarify the same!

                                                             


                                                            CONCATENATE (SecurityTable)
                                                            LOAD DISTINCT
                                                            'USER' as [ACCESS],
                                                            upper(account) as [USERID],
                                                            upper(account) as [PASSWORD],
                                                            upper(account) as SecurityAccount,
                                                            '*' as SecurityMfa // Let me know what it does. What data will it will retrieve.
                                                            resident TempTab;


                                                            Thanks and Regards,

                                                            Rikab

                                                            • Problem in loading the admin login of my document! Help needed very urgently!

                                                              Hi Rakesh!

                                                              Sorry for asking silly doubts like the one which i have asked below. '*' means it will show the data of all the manufacturers. Myself felt bad when for not knowing this before posting this query.

                                                               

                                                              <pre>CONCATENATE (SecurityTable)
                                                              LOAD DISTINCT
                                                              'USER' as [ACCESS],
                                                              upper(account) as [USERID],
                                                              upper(account) as [PASSWORD],
                                                              upper(account) as SecurityAccount,
                                                              '*' as SecurityMfa // Let me know what it does. What data will it will retrieve.
                                                              resident TempTab;

                                                               



                                                              Regards,

                                                              Rikab

                                                               

                                                              • Problem in loading the admin login of my document! Help needed very urgently!

                                                                Hi Rakesh!

                                                                Thanks for your effort. I have done the required changes in my document and it is working same like yours. Though the login is working i need to check the result. As in your document you considered only account as the link between both tables but in my document i used both the account and mfaccode as the link, so need to check the result set first.

                                                                1. When i use the account:001001 and the mfaccode:2021 it works now and previously also it was working. But need to check the result set.

                                                                2. I have not loaded the admin login in document yet due to sythetic key issue. But as per you, you have considered user with id:001001 and pwd:001001 acts as a admin of 001001. So need to check how to pull the admin data in to document and then check whether the same document is working or not.

                                                                3. To see the overall data you have added id:admin and pwd:admin. Though it is not required in the current document it will be definitely useful in the future.(Thanks for that)

                                                                Now I want to sheet wise access according to the logged in user. Let me know how to do that please.

                                                                Thanks for your effort and your precious time.

                                                                Waiting for your reply!

                                                                Thanks and Regards,

                                                                Rikab

                                                                 

                                                                  • Problem in loading the admin login of my document! Help needed very urgently!

                                                                    Hi Rakesh!

                                                                    There has been lots of variation in the result. I think this may be due to the %key field. As In my document I used to connect the tables using account+mfacode but I think where as in yours it is connected with %key field.

                                                                    After reloading both the old document and your document. If i select account:001001 mfaccode:2021 it is showing exact value in both. But if I login with the same account and mfaccode in both the document there has been huge mismatch in the result. Please try to let me know the cause of this problem.

                                                                    Old document:

                                                                    New Document:

                                                                    Hope you can see the difference between both. Please check why is happening like that.

                                                                    Thanks and Regards,

                                                                    Rikab

                                                                  • Problem in loading the admin login of my document! Help needed very urgently!

                                                                    Hello Rakesh!

                                                                    I tried here to load the admin login from mfac_mst as i don't wanted to load from inline function as the login information will keep changing. But the problem here is it looks like the same way we used to load the user login information.

                                                                    http://community.qlik.com/forums/t/27224.aspx

                                                                     


                                                                    // 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;

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


                                                                     


                                                                    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;
                                                                    // New one for admin
                                                                    CONCATENATE (SecurityTable)
                                                                    LOAD DISTINCT
                                                                    'USER' as [ACCESS],
                                                                    upper([userid]) as [USERID],
                                                                    upper([password]) as [PASSWORD],
                                                                    upper(account) as SecurityAccount,
                                                                    '*' as SecurityMfa,
                                                                    'Some' as SecurityPage
                                                                    resident TempTab;
                                                                    // Old one for admin. Here there is a difference so we can clearly differentiate. But for the above one there is no difference.

                                                                    CONCATENATE (SecurityTable)
                                                                    LOAD DISTINCT
                                                                    'USER' as [ACCESS],
                                                                    upper(account) as [USERID],
                                                                    upper(account) as [PASSWORD],
                                                                    upper(account) as SecurityAccount,
                                                                    '*' as SecurityMfa,
                                                                    'Some' as SecurityPage
                                                                    resident TempTab;



                                                                    Please help me out!

                                                                    Thanks in advance,

                                                                    Rikab

                                                                    • Problem in loading the admin login of my document! Help needed very urgently!

                                                                      Hello Rakesh,

                                                                      Please help me out I am waiting for your solution.

                                                                      Thanks and Regards,

                                                                      Rikab