Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

vhtsanden
New Contributor II

LOAD function empties a table

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

3 Replies
MVP
MVP

Re: LOAD function empties a table

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vhtsanden
New Contributor II

Re: LOAD function empties a table

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?

vhtsanden
New Contributor II

Re: LOAD function empties a table

I found the solution as described in the following threat: Dynamically LOAD data, somehow LOAD disturbs the process

Community Browser