Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
g23
New Contributor

Dynamically Loading Multiple Excel Files

Hi all,

I'm trying to write a script that loads me all fields from some excels file but when I call the SUB PERGAR_MINISTAT_NPC in the script but my script is not calling correctly the SUB.

I attach the script I wrote:

SUB PERGAR_MINISTAT_NPC;

let vSubName = 'PERGAR_MINISTAT_NPC';

//set v_Concatenate ='';
For Each vFileName in FileList ($(vPathInput\PERGAR\pergar2_*$(vExt));

Trace -----------Inizio Sub Ministat NPC --------;
//$(v_Concatenate)

[$(vTableNamePergar)]:
load * from [$(vPathInput)\PERGAR\pergar2_*$(vExt)]
//'$(vFileName)'
(ooxml, embedded labels);

concatenate ($(vTableNamePergar));
//set v_Concatenate ='Concatenate';


NEXT vFileName

Trace --------------- Fine Caricamento $(vFileName) ---------------;

if NoOfRows('$(vTableNamePergar)')>0 then
store [$(vTableNamePergar)] into [$(vPathQVD1)/$(vTableNamePergar).qvd];
DROP TABLE [$(vTableNamePergar)];
else
trace DATASET $(vTableNamePergar) VUOTO ---------------------------;
End If

Trace --------------- Fine Sub $(vSubName) ---------------;

END SUB

can someone help me understand who to make my script work??

Thanks

7 Replies
Partner
Partner

Re: Dynamically Loading Multiple Excel Files

your sub: "SUB PERGAR_MINISTAT_NPC" can do without";"
also you need to call the sub where needed.
so: CALL PERGAR_MINISTAT_NPC
g23
New Contributor

Re: Dynamically Loading Multiple Excel Files

I deleted the ; from the SUB and I was already calling the SUB but I still have the same error.

Any other advice?

Partner
Partner

Re: Dynamically Loading Multiple Excel Files

whats the error? and it seems like you are trying to have some inputs like "vPathInput" are these just variables or actual inputs in the call statement?
g23
New Contributor

Re: Dynamically Loading Multiple Excel Files

This is the error:

Script error.PNG

and the vpathInput ecc.. are the variables that I created in another script.

I'll attach the script of the variable:

SUB VARIABILI

let vSubName = 'VARIABILI';

Trace --------------- Inizio Sub $(vSubName) ---------------;

//Configurazione path
let vRoot = 'lib://SelfBI/PC\MINISTAT_NPC';
let vPathInput = '$(vRoot)\INPUT';
let vPathQVD1 = '$(vRoot)\QVD\QVD 1';
let vPathQVD2 = '$(vRoot)\QVD\QVD 2';
let vPathUtils = '$(vRoot)\UTILS';

//Files name
let vFileName = 'pergar2_*.$(vExt)';

// Formato file di input
let vExt = '.xlsx';
let vCsv = '.csv';

// Nome Tabella (file .qvd di output)
let vTableNamePergar = 'PERGAR';
let vTableNamePerprod = 'PERPROD';
let vTableNameTuttegar = 'TUTTEGAR';

Trace vPathInputPergar: $(vPathInputPergar);
Trace vPathInputPerprod: $(vPathInputPerprod);
Trace vPathInputTuttegar: $(vPathInputTuttegar);
Trace vPathQVD1: $(vPathQVD1);
Trace vPathQVD2: $(vPathQVD2);
Trace vPathUtils: $(vPathUtils);

Trace --------------- Fine Sub $(vSubName) ---------------;
END SUB;

Partner
Partner

Re: Dynamically Loading Multiple Excel Files

I assume you have the sub before the call?
g23
New Contributor

Re: Dynamically Loading Multiple Excel Files

Yess the call script is at the end of the sub. 

Partner
Partner

Re: Dynamically Loading Multiple Excel Files

Hi,

There is probably something wrong in your sequence. Try to comment out ALL the script and do this:

SUB Test

Trace >>>> This is a test!;

END SUB

Put this at the end of your script:

CALL Test;

 Now try to run your script and continue from this.

Jordy

Climber

Work smarter, not harder