Dynamically LOAD data, somehow LOAD disturbs the process

[Anonymous]

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

[swuehl]

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.

[Anonymous]

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?

[swuehl]

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

lib://Factset/QLIK_COMPOSITECGFGMFAC_MSCIAllCountryWorld_1Q_0Q.XLS ?

[Anonymous]

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.

[Anonymous]

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?

[swuehl]

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

[Anonymous · Accepted solution]

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;

[swuehl]

Good to hear that it works.

If you want, you can flag answers as helpful or correct

Qlik Community Tip: Marking Replies as Correct or Helpful