LOAD function empties a table

Anonymous — Wed, 03 Feb 2016 01:38:31 GMT

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

Re: LOAD function empties a table

jonathandienst — Wed, 03 Feb 2016 06:17:42 GMT

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.

Re: LOAD function empties a table

Anonymous — Wed, 03 Feb 2016 10:33:13 GMT

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 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 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... BENCHMARK2

## reading the data list number 2

name of tabel: DataList

get parameters from the datatable
## starting the if statement

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... 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?

Re: LOAD function empties a table

Anonymous — Wed, 03 Feb 2016 20:36:25 GMT

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

topic Re: LOAD function empties a table in Connectivity & Data Prep

LOAD function empties a table

Re: LOAD function empties a table

Re: LOAD function empties a table

Re: LOAD function empties a table