9 Replies Latest reply: Feb 3, 2016 3:40 PM by Stefan Wühl RSS

    Dynamically LOAD data, somehow LOAD disturbs the process

    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.

       

      I resubmitted my answers to 'new to qliksense' because this is most likely a beginners mistake.

       

      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';
         

      for i = 0 to (30-1) step 1


          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: Dynamically LOAD data, somehow LOAD disturbs the process
          Stefan Wühl

          I believe Qlik will read something like

           

          '$(vVar)' AS FIELD

           

          as assigning the expanded variable as constant value to the field record, not using the expanded value as field name reference. Hence remove the single quotes and use [ ] instead:

           

              [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);


          Same for your second load.

            • Re: Dynamically LOAD data, somehow LOAD disturbs the process
              Vincent Van der Sanden

              Thank you for your answer, I changed it to::

               

                      [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);

               

              It does fundamentally change the code when I change this but it's still somehow not working. I get the following error:

               

              The following error occurred:

              Field not found - <Period1_Period2>

              The error occurred here:

              [Data]:
                      noConcatenate LOAD [Period1_Period2] as DatumType, [Accountname1] as Account, [Benchmark1] as BenchmarkWeightingScheme,*   
                      FROM [lib://Factset/QLIK_COMPOSITECGFGMFAC_MSCIAllCountryWorld_1Q_0Q.XLS]
                      (biff, embedded labels, header is 5 lines, table is @1)

               

               

               

              It somehow includes the brackets which should not be included? Is there a different way to do this? Could I put the 'PEEK' in the LOAD script instead? Do I need to use the LET ?

               

              Somehow people always use a LET and then a TRACE with the parameter in the LET to be shown in the TRACE.

               

              Why doesn't anyone combine both in 1 statement?

                • Re: Dynamically LOAD data, somehow LOAD disturbs the process
                  Stefan Wühl

                  Is there a field called Period1_Period2 in the table you are loading

                  lib://Factset/QLIK_COMPOSITECGFGMFAC_MSCIAllCountryWorld_1Q_0Q.XLS ?

                    • Re: Dynamically LOAD data, somehow LOAD disturbs the process
                      Vincent Van der Sanden

                      Yes there is. That field doesn't make a lot of sense but I didn't want to disclose the 'real' information I used in this project.

                        • Re: Dynamically LOAD data, somehow LOAD disturbs the process
                          Vincent Van der Sanden

                          I am getting the idea that the underscores in the variablenames somehow disturb the process. The filename also contains underscores. Could data within those fields disturb the process?

                            • Re: Dynamically LOAD data, somehow LOAD disturbs the process
                              Stefan Wühl

                              The variable expansion seems correct to me.

                               

                              If you create a LOAD statement for this table mentioned above using the wizard, how does the scipt statement looks like (I am assuming the table then loads correctly)?

                                • Re: Dynamically LOAD data, somehow LOAD disturbs the process
                                  Vincent Van der Sanden

                                  Eventually I played with the debugger and I created a new app called FILE_LOADER.


                                  This app had the same problems but I removed all comments and totally cleaned it up and after changing the [Data] section it suddenly worked! Thank you a lot swuehl for pointing out to this part of my code.

                                   

                                  Instead of [] on the left part (expanded variable) I added '' to the right part (columnname) as shown in the code below. Somehow I want to have one extra column that describes the 'source' of the data which can be used in the application as a filter.

                                   

                                        [Data]:

                                           noConcatenate LOAD '$(DatumType_field)' as 'DatumType', '$(Account_field)' as 'Account', '$(BenchmarkWeightingScheme_field)' as 'BenchmarkWeightingScheme',*

                                            FROM 'lib://QLIKXLSX/$(FileName_field)'

                                            (biff, embedded labels, header is 5 lines, table is @1);

                                   

                                  Maybe to help others, I will pate the entire code (that works) below. swuehl can I give you points for pointing me in the right direction? It did save me a lot of headache!

                                   

                                   

                                  SET ThousandSep=',';
                                  SET DecimalSep='.';
                                  SET MoneyThousandSep=',';
                                  SET MoneyDecimalSep='.';
                                  SET MoneyFormat='$#,##0.00;($#,##0.00)';
                                  SET TimeFormat='h:mm:ss TT';
                                  SET DateFormat='M/D/YYYY';
                                  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
                                  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                                  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
                                  SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
                                  SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
                                  SET FirstWeekDay=6;
                                  SET BrokenWeeks=1;
                                  SET ReferenceDay=0;
                                  SET FirstMonthOfYear=1;
                                  SET CollationLocale='en-US';

                                  Trace #### Start ####;

                                  trace ## Loading DataList;
                                     
                                  ListOfFiles:
                                  LOAD FactsetFileNameField, DatumTypeField, AccountField, BenchmarkWeightingSchemeField
                                  FROM [lib://QLIKXLSX/FileList.xlsx]
                                  (ooxml, embedded labels, table is Sheet1);
                                     
                                  // define nameOfField, which can be used for later reference
                                  Let nameOfTable = 'ListOfFiles';

                                  trace ## start the for loop;

                                  for i = 0 to (noofrows('$(nameOfTable)')-1) step 1
                                  //for i = 0 to (5) step 1
                                      trace ## reading the data list number $(i); //read DataList information
                                      trace name of tabel: $(nameOfTable); //give the table a name. This makes it easier to point to this table in a later stage.
                                      trace get parameters from the datatable;
                                     
                                      Let FileName_field = Peek('FactsetFileNameField', $(i), '$(nameOfTable)'); //read row i from the 'data' part of the table with filenames
                                      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;
                                     
                                      Trace Processing... $(FileName_field);
                                      Trace Processing... $(DatumType_field);
                                      Trace Processing... $(Account_field);
                                      Trace Processing... $(BenchmarkWeightingScheme_field);
                                     
                                     if i=0 then
                                       
                                        [Data]:
                                           noConcatenate LOAD '$(DatumType_field)' as 'DatumType', '$(Account_field)' as 'Account', '$(BenchmarkWeightingScheme_field)' as 'BenchmarkWeightingScheme',*
                                            FROM 'lib://QLIKXLSX/$(FileName_field)'
                                            (biff, embedded labels, header is 5 lines, table is @1);
                                       
                                     elseif i>0 then
                                       
                                           Concatenate(Data)
                                        LOAD '$(DatumType_field)' as 'DatumType', '$(Account_field)' as 'Account', '$(BenchmarkWeightingScheme_field)' as 'BenchmarkWeightingScheme',*   
                                            FROM 'lib://QLIKXLSX/$(FileName_field)'
                                            (biff, embedded labels, header is 5 lines, table is @1);
                                     end if
                                     
                                  next i

                                  exit script;