1 Reply Latest reply: Oct 3, 2017 2:09 PM by Ruben Marin RSS

    Data Connection

    Mohammed Mustaq Ali

      Dears,

       

      i have following folder structure:

       

                           Data:

                                   Company A

                                              2016 (Excel File)

                                               2017 (Excel File)

                                   Company B

                                              2016 (Excel File)

                                               2017 (Excel File)


                                    and so on ....... ( i have almost 20 Companies Data )


      From all the Excel Files i am reading Data from Yearly Tab.


      Instead of loading data from each excel file separately i have created loop to read the data which is working fine with below script.

       

       

      Set vConcatenate = ;

      FileList:

      LOAD

      '' AS SourceFile

      AUTOGENERATE 0;

      sub ScanFolder(Root)

      for each FileExtension in 'xlsx'

      for each FoundFile in filelist(Root & '\*.' & FileExtension)

       

      Table:

      LOAD

      '$(FoundFile)' as SourceFile,

          'Company ' &SubField(FileBaseName(),'_',1) as CompanyName,

          Right(FileBaseName(),4) as Year,

          Product,

          SALES

      FROM

      $(FoundFile)

      (ooxml, embedded labels, table is Yearly);

                          

      Set vConcatenate = Concatenate;

      next FoundFile

      next FileExtension

      for each SubDirectory in dirlist( Root & '\*' )

      call ScanFolder(SubDirectory)

      next SubDirectory

      end sub

      call ScanFolder('lib://COMPANYDATA');

       


      it's working fine on Qliksense Desktop  Application.

       

      but when i moved the application to Server and Created the new connection with same name (COMPANYDATA) it's added Username to the connection :


      ('lib://COMPANYDATA (mohammedmustaq_Pc) ');


      and i updated connection in the script:

       

       

      Set vConcatenate = ;

      FileList:

      LOAD

      '' AS SourceFile

      AUTOGENERATE 0;

      sub ScanFolder(Root)

      for each FileExtension in 'xlsx'

      for each FoundFile in filelist(Root & '\*.' & FileExtension)

       

      Table:

      LOAD

      '$(FoundFile)' as SourceFile,

          'Company ' &SubField(FileBaseName(),'_',1) as CompanyName,

          Right(FileBaseName(),4) as Year,

          Product,

          SALES

      FROM

      $(FoundFile)

      (ooxml, embedded labels, table is Yearly);

                          

      Set vConcatenate = Concatenate;

      next FoundFile

      next FileExtension

      for each SubDirectory in dirlist( Root & '\*' )

      call ScanFolder(SubDirectory)

      next SubDirectory

      end sub

      call ScanFolder('lib://COMPANYDATA (mohammedmustaq_Pc) ');



      when Loading application Getting the below error on server

       

       

      Error.JPG

       

       

       

      Thanks