Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am very new to Qlik Sense but I am trying to get the hang of it. I might be making a very fundamental mistake but I need help advice.
The following script reads a DataList with filenames, and I would like to loop through this table with the peek function and load all the data in one big table. I created a test loop and it seems fine, though when I enable the LOAD part (there where i=0) something goes wrong. Because the second loop all parameters seem to be empty. Strangely enough this only happens when this LOAD part is enabled.
What am I doing wrong???
trace ## Loading DataList;
DataList:
LOAD
FactsetFileNameField,
DatumTypeField,
AccountField,
BenchmarkWeightingSchemeField
FROM [lib://QLIKXLSX/GM FAC Table.xlsx]
(ooxml, embedded labels, table is Sheet1);
trace ## define tabelnaam;
Let nameOfTable = 'DataList';
// exit script;
//nameOfTable is the name of the table we are going to process
// LET nameOfTable = 'FileOverview';
// Trace ROW 19 ## FileOverview looop;
//init i on 0
// Let i = 0;
//i got crazy of errors, just put 30 in the hard way
trace ## start the for loop;
//for i = 0 to (noofrows('$(nameOfTable)')-1) step 1
for i = 0 to (30-1) step 1
//read DataList information
trace ## reading the data list number $(i);
trace name of tabel: $(nameOfTable);
trace get parameters from the datatable;
Let FactsetFileName_field = Peek('FactsetFileNameField', $(i), $(nameOfTable));
Let DatumType_field = Peek('DatumTypeField', $(i), $(nameOfTable));
Let Account_field = Peek('AccountField', $(i), $(nameOfTable));
Let BenchmarkWeightingScheme_field = Peek('BenchmarkWeightingSchemeField', $(i), $(nameOfTable));
trace ## starting the if statement;
if i=0 then
Trace Processing... $(FactsetFileName_field);
Trace Processing... $(DatumType_field);
Trace Processing... $(Account_field);
Trace Processing... $(BenchmarkWeightingScheme_field);
// [Data]:
// noConcatenate LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
// FROM [lib://Factset/$(FactsetFileName_field)]
// (biff, embedded labels, header is 5 lines, table is @1);
elseif i>0 then
Trace Processing... $(FactsetFileName_field);
Trace Processing... $(DatumType_field);
Trace Processing... $(Account_field);
Trace Processing... $(BenchmarkWeightingScheme_field);
// Concatenate(Data)
// LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
// FROM [lib://Factset/$(FactsetFileName_field)]
// (biff, embedded labels, header is 5 lines, table is @1);
end if
// exit script;
next i
The only problem that I see in your code is that the Peeks need some extra quotes for the 3rd argument:
Let FactsetFileName_field = Peek('FactsetFileNameField', $(i), '$(nameOfTable)');
I assume that the load into DataList returns some records.
I changed the quotes as suggested by jonathan dienst, but it still doesn't work. I have included the TOTAL script and also the total load progress log.
My question is what am I doing wrong and am I doing it the right way (as others would design this process)?
Executing the following script:
trace ## Loading DataList;
DataList:
LOAD
FactsetFileNameField,
DatumTypeField,
AccountField,
BenchmarkWeightingSchemeField
FROM [lib://QLIKXLSX/GM FAC Table.xlsx]
(ooxml, embedded labels, table is Sheet1);
trace ## define tabelnaam;
Let nameOfTable = 'DataList';
// exit script;
//nameOfTable is the name of the table we are going to process
// LET nameOfTable = 'FileOverview';
// Trace ROW 19 ## FileOverview looop;
//init i on 0
// Let i = 0;
//i got crazy of errors, just put 30 in the hard way
trace ## start the for loop;
//for i = 0 to (noofrows('$(nameOfTable)')-1) step 1
//for i = 0 to (30-1) step 1
for i = 0 to (6-2) step 1
//read DataList information
trace ## reading the data list number $(i);
trace name of tabel: $(nameOfTable);
trace get parameters from the datatable;
Let FactsetFileName_field = Peek('FactsetFileNameField', $(i), $(nameOfTable));
Let DatumType_field = Peek('DatumTypeField', $(i), $(nameOfTable));
Let Account_field = Peek('AccountField', $(i), $(nameOfTable));
Let BenchmarkWeightingScheme_field = Peek('BenchmarkWeightingSchemeField', $(i), '$(nameOfTable)');
trace ## starting the if statement;
if i=0 then
Trace Processing... $(FactsetFileName_field);
Trace Processing... $(DatumType_field);
Trace Processing... $(Account_field);
Trace Processing... $(BenchmarkWeightingScheme_field);
// [Data]:
// noConcatenate LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
// FROM [lib://Factset/$(FactsetFileName_field)]
// (biff, embedded labels, header is 5 lines, table is @1);
elseif i>0 then
Trace Processing... $(FactsetFileName_field);
Trace Processing... $(DatumType_field);
Trace Processing... $(Account_field);
Trace Processing... $(BenchmarkWeightingScheme_field);
// Concatenate(Data)
// LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
// FROM [lib://Factset/$(FactsetFileName_field)]
// (biff, embedded labels, header is 5 lines, table is @1);
end if
// exit script;
next i
exit script;
Results in the following data load progress log :
Started loading data
#### Start ####
## Loading DataList
DataList << Sheet1
Lines fetched: 5
## define tabelnaam
## start the for loop
## reading the data list number 0
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing... FILENAME1
Processing... DATETYPE1
Processing... ACCOUNT1
Processing... BENCHMARK1
## reading the data list number 1
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing... FILENAME2
Processing... DATETYPE2
Processing... ACCOUNT2
Processing... BENCHMARK2
## reading the data list number 2
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing... FILENAME3
Processing... DATETYPE3
Processing... ACCOUNT3
Processing... BENCHMARK3
## reading the data list number 3
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing... FILENAME4
Processing... DATETYPE4
Processing... ACCOUNT4
Processing... BENCHMARK4
## reading the data list number 4
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing... FILENAME5
Processing... DATETYPE5
Processing... ACCOUNT5
Processing... BENCHMARK5
App saved
Finished successfully
0 forced error(s)
0 synthetic key(s
But when Executing the following script:
trace ## Loading DataList;
DataList:
LOAD
FactsetFileNameField,
DatumTypeField,
AccountField,
BenchmarkWeightingSchemeField
FROM [lib://QLIKXLSX/GM FAC Table.xlsx]
(ooxml, embedded labels, table is Sheet1);
trace ## define tabelnaam;
Let nameOfTable = 'DataList';
// exit script;
//nameOfTable is the name of the table we are going to process
// LET nameOfTable = 'FileOverview';
// Trace ROW 19 ## FileOverview looop;
//init i on 0
// Let i = 0;
//i got crazy of errors, just put 30 in the hard way
trace ## start the for loop;
//for i = 0 to (noofrows('$(nameOfTable)')-1) step 1
//for i = 0 to (30-1) step 1
for i = 0 to (6-2) step 1
//read DataList information
trace ## reading the data list number $(i);
trace name of tabel: $(nameOfTable);
trace get parameters from the datatable;
Let FactsetFileName_field = Peek('FactsetFileNameField', $(i), $(nameOfTable));
Let DatumType_field = Peek('DatumTypeField', $(i), $(nameOfTable));
Let Account_field = Peek('AccountField', $(i), $(nameOfTable));
Let BenchmarkWeightingScheme_field = Peek('BenchmarkWeightingSchemeField', $(i), '$(nameOfTable)');
trace ## starting the if statement;
if i=0 then
Trace Processing... $(FactsetFileName_field);
Trace Processing... $(DatumType_field);
Trace Processing... $(Account_field);
Trace Processing... $(BenchmarkWeightingScheme_field);
[Data]:
noConcatenate LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
FROM [lib://Factset/$(FactsetFileName_field)]
(biff, embedded labels, header is 5 lines, table is @1);
elseif i>0 then
Trace Processing... $(FactsetFileName_field);
Trace Processing... $(DatumType_field);
Trace Processing... $(Account_field);
Trace Processing... $(BenchmarkWeightingScheme_field);
// Concatenate(Data)
// LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
// FROM [lib://Factset/$(FactsetFileName_field)]
// (biff, embedded labels, header is 5 lines, table is @1);
end if
// exit script;
next i
exit script;
Will result in the following data load progress log :
Started loading data
#### Start ####
## Loading DataList
DataList << Sheet1
Lines fetched: 5
## define tabelnaam
## start the for loop
## reading the data list number 0
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing... Realfilename.XLS
Processing... DATETYPE1
Processing... ACCOUNT1
Processing... BENCHMARK1
Data << Realfilename$
Lines fetched: 2,739
## reading the data list number 1
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing...
Processing...
Processing...
Processing... BENCHMARK2
## reading the data list number 2
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing...
Processing...
Processing...
Processing... BENCHMARK3
## reading the data list number 3
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing...
Processing...
Processing...
Processing... BENCHMARK4
## reading the data list number 4
name of tabel: DataList
get parameters from the datatable
## starting the if statement
Processing...
Processing...
Processing...
Processing... BENCHMARK5
App saved
Finished successfully
0 forced error(s)
0 synthetic key(s)
The difference between the 2 scrips is the following part:
[Data]:
noConcatenate LOAD '$(DatumType_field)' as DatumType, '$(Account_field)' as Account, '$(BenchmarkWeightingScheme_field)' as BenchmarkWeightingScheme,*
FROM [lib://Factset/$(FactsetFileName_field)]
(biff, embedded labels, header is 5 lines, table is @1);
Somehow, this part will causew that the fiels $(FactsetFileName_field), $(DatumType_field), $(Account_field) are empty.
Strangely enough $(BenchmarkWeightingScheme_field) does work.
What am I doing wrong?
I found the solution as described in the following threat: Dynamically LOAD data, somehow LOAD disturbs the process