Data Connection

[MohammedMustaq]

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

Thanks

[rubenmarin]

Hi Mohammed, I don't see why it won't work in server and I don't have a server to test, check in script editor if the connection really exists.

Qlik help gives a very similar code:

For each..next ‒ Qlik Sense