2 Replies Latest reply: Nov 17, 2016 3:50 AM by Court van de Lisdonk RSS

    Generic Load

    Court van de Lisdonk

      Hello,

       

      After great help from Marcus Sommer about my problems/questions in case of Section Access, I am landed at the next episode in my search for a good section access

       

      At the moment my Section Access looks as followed:

       

      /* Controlling: The question if someone is allow to start the document is cleared in the   Star is '-1';

      SECTION Access;

      LOAD * INLINE [
      ACCESS, USERNAME, PASSWORD
      ADMIN, QADMIN, QADMIN
      ];  
        LOAD
      if ((ALLDIMENSIONVALUESALLOWED = '-1'), 'ADMIN', 'USER') as ACCESS,
      USERNAME,
      PASSWORD
      FROM [C:\Qlikview\Datasources\qvd\AccessAllowance.qvd] (qvd);

      Problem in the AccessAllowance.qvd is that I have two columns: one with dimension names and the second with dimension values. I have four dimensions at the moment: Company, Division, SalesDistrict and Customer

       

      I have 4 groups of users:

      1. 1. Users who are allowed all values in all dimensions
      2. 2. Users who are allowed to see only some values in one dimension and all values in the other dimensions
      3. 3. Users who are allowed to see only some values in all the dimensions
      4. 4. Users who are allowed to see only values from other dimensions like production, products

      So Marcus told me to use a Generic Load to transform my accessfile. I opened the document: The Generic Load from Henric Cronström in Qlik Design Blog am 01.04.2014 01:46:18. And I have created a new Qlikview Document with the following script:

      // Generic Load
      GenericDB:
      Generic LOAD
      USERPK,
      DIMENSIONNAME,
      DIMENSIONVALUEALLOWED
      FROM [C:\Qlikview\Datasources\qvd\GenericAccesfile.qvd] (qvd);

      The GenericLoad creates 4 tables, for each dimension one table. I don’t have a synthetic key because I have only one key USERPK. The Tables I have are GenericDB.Company, GenericDB.Division, GenericDB.SalesDistrict and GenericDB.Customers. And two OrphanTables: $orphan_DIMENSIONNAME and $ orphan_DIMENSIONVALUEALLOWED

       

      For my Control Access I want to have one file, so I build in the For-Next loop from the document:

       

       

      // Make one Table for Control Access:
      Set vListOfTables = ;

      FOR VTableNo = 0 to NoOfTables()
      Let vTableName = TableName($(vTableNo));
      If SubField(vTableName, '.',1) = 'GenericLabel' Then
      Let vListOfTables = vListOfTables & If(Len(vListOfTables) > 0,',') & Chr(39) &  vTableName & Chr(39);
      End If ;
      NEXT vTableNo

      CombinedGenericTable:
      LOAD Distinct USERPK From GenericDB;

      For Each vTableName in $(vListOfTables)
      Left Join(CombinedGenericTable) Load * Resident [$(vTableName)];
      Drop Table [$(vTableName)];
      Next vTableName

       

      When I run the script I get an errormessage: NEXT vTableNo and a following errormessage:

      Error Opening File 'C:\Qlikview\Development\GenericDB'  System cannot find that File

      CombinedGenericTable:

      LOAD Distinct USERPK From GenericDB

       

      My question is can someone explain what is wrong with my script and what should I do to get it work?

       

      Thanks in regards