3 Replies Latest reply: Feb 3, 2016 3:36 PM by Vincent Van der Sanden RSS

    LOAD function empties a table

    Vincent Van der Sanden

      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
          Jonathan Dienst

          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
            Vincent Van der Sanden

            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?