2 Replies Latest reply: Jul 18, 2016 3:17 AM by Marcus Sommer RSS

    Entire load statement in a Variable

    Paul Schmidt

      Hi All

       

      I have been developing a Qlik Sense solution that reads metadata from a Excel spreadsheet for source and target fields. So, the process is:

       

      1. Import metadata objects

      2. Loop through each object and add a load statement into a variable, see load statement below:

       

      Sub Load_Statement_Generate(pQVDTarget, pTableObject, pOutColumns)

        $(pQVDTarget)Columns:

          Load Concat(SourceField & ' AS ' & '[' & TargetField & ']', ', ', SortOrder) As $(pQVDTarget)Columns

          Resident '$(pTableObject)' Where (TargetName = '$(pQVDTarget)');

        Let pOutColumns = Peek('$(pQVDTarget)Columns', 0, '$(pQVDTarget)Columns');

        Call Table_Drop('$(pQVDTarget)Columns');

      End Sub;

       

      3. Generate table using pOutColumns, see below

       

      Sub Table_Generate_Dimension(pQVDTarget, pExtractPath, pTenant, pQVDSource, pColumns)

        $(pQVDTarget): 

          Load

          $(pColumns) 

          From [$(pExtractPath)/$(pTenant)/$(pQVDSource).qvd] (qvd);

      End Sub;

       

      This works OK but starts to fall apart when the source fields have:

       

      - Spaces, e.g Employee Name or

      - Functions, e.g. Autonumber(%EmployeeKey)

       

      Does anyone have a better way to generate a load statement from source and target metadata?

       

      See example metadata below:

       

            

      SourceTargetNameSourceFieldTargetFieldStageTypeSortOrderObjectType
      IGDimCashierAutonumber(%CashierKey)%CashierKeyPresentation1Dim
      IGDimCashierCashier IDCashier IDPresentation2Dim
      IGDimCashierCashier NameCashier NamePresentation3Dim

       

      Best, Paul

        • Re: Entire load statement in a Variable
          Marcus Sommer

          In general the approach to put load-parts or a whole load-statement into variables worked. But you need to be carefully by the syntax respectively you need more to use the debugger, the log-file and/or a trace-statement to ensure the syntax (from the variables) is correct and to find the reasons for an error.

           

          I couldn't sse any issue related to functions but the failure regarding to spaces within fieldnames will be caused from missing square-brackets:

           

          Sub Load_Statement_Generate(pQVDTarget, pTableObject, pOutColumns)

            $(pQVDTarget)Columns:

              Load Concat('[' & SourceField & '] AS ' & '[' & TargetField & ']', ', ', SortOrder) As $(pQVDTarget)Columns

              Resident '$(pTableObject)' Where (TargetName = '$(pQVDTarget)');

            Let pOutColumns = Peek('$(pQVDTarget)Columns', 0, '$(pQVDTarget)Columns');

            Call Table_Drop('$(pQVDTarget)Columns');

          End Sub;

           

          Edit: If I look again on your table with source/target-fields I notice that you want apply the function within the source-field. I won't say that there would be no way to achieve it within these field but I suggest to separate a function from a field. I do something quite similar and I use beside source/target-fields one field for a function and a further one for functions-parameter like: 'DD/MM/YYYY' - and yes this will make your load-statement more complex because you will need additionally to query if there are functions or not. I think I have posted it here within the community but at the moment I couldn't find it.

           

          - Marcus

            • Re: Entire load statement in a Variable
              Marcus Sommer

              Here is the script which I use to transform some of my rawdata and I hope it will be helpful for you:

               

              /*---------------------------------------------------------------------------------------------------------------------------------------------------------------

              File-Liste einlesen

              ---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

               

              let vLoadStart = $(eLoadStart);

              [RawDataFiles]:

              LOAD

                  [ID], [SourcePath], [SourceFile], [SourceFileFormat], [SourceCodePage], [SourceLabels], [SourceDelimiter], [MSQ], [TargetPath],

                  [TargetFile], [TargetFileFormat]

              FROM $(pApp@L0@Con)PrepareRawDataInformations.xlsx ($(typXLSX)Files);

              $(Include=$(pScript)Load\Include_LoadLogging.txt);

               

              /*---------------------------------------------------------------------------------------------------------------------------------------------------------------

              Felder-Liste einlesen

              ---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

               

              let vLoadStart = $(eLoadStart);

              [RawDataFields]:

              LOAD [ID], [Sort], [FieldNameOrigin], [FieldnamePrepare], [Transform], [TransformFormat]

              FROM $(pApp@L0@Con)PrepareRawDataInformations.xlsx ($(typXLSX)Fields);

              $(Include=$(pScript)Load\Include_LoadLogging.txt);

               

              /*---------------------------------------------------------------------------------------------------------------------------------------------------------------

              Felder-Liste verketten

              ---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

               

              let vLoadStart = $(eLoadStart);

              [LoadFields]:

              Load [ID], concat(if(len([Transform]) >= 1, [Transform] & '(' & [FieldNameOrigin] & ', ' & [TransformFormat] & ') as ' & [FieldnamePrepare],

                                  [FieldNameOrigin] & ' as ' & [FieldnamePrepare]), ', ' & chr(10), [Sort]) as [LoadFields]

              Resident [RawDataFields] Group By [ID];

              $(Include=$(pScript)Load\Include_LoadLogging.txt);

               

              /*---------------------------------------------------------------------------------------------------------------------------------------------------------------

              Generierung von dynamischen Loadscripten

              ---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

               

              for i = 0 to NoOfRows('RawDataFiles') - 1

                  let vCurrentPeriod = year(today() - 1) & '_' & num(month(today() - 1), '00');

                 

                  let vID = peek('ID', $(i), 'RawDataFiles');

                  let vSourcePath = peek('SourcePath', $(i), 'RawDataFiles');

                  let vSourceFile = replace(peek('SourceFile', $(i), 'RawDataFiles'), 'YYYY_MM', '$(vCurrentPeriod)');

                  let vSourceFileFormat = peek('SourceFileFormat', $(i), 'RawDataFiles');

                  let vSourceCodePage = peek('SourceCodePage', $(i), 'RawDataFiles');

                  let vSourceLabels = peek('SourceLabels', $(i), 'RawDataFiles');

                  let vSourceDelimiter = peek('SourceDelimiter', $(i), 'RawDataFiles');

                  let vMSQ = peek('MSQ', $(i), 'RawDataFiles');

               

                  let vTargetPath = peek('TargetPath', $(i), 'RawDataFiles');

                  let vTargetFile = replace(peek('TargetFile', $(i), 'RawDataFiles'), 'YYYY_MM', '$(vCurrentPeriod)');

                  let vTargetFileFormat = peek('TargetFileFormat', $(i), 'RawDataFiles');

                  let vTargetComplete = '$(vTargetPath)' & '$(vTargetFile)' & ' ($(vTargetFileFormat), delimiter is \t)';

                 

                  set vConvertPathFile = '"$(vTargetPath)$(vTargetFile)"';

                     

                  let vTableName = subfield('$(vSourceFile)', '.', 1);

                  let vLoadFields = 'Load ' & chr(10) & Lookup('LoadFields', 'ID', $(vID), 'LoadFields') & chr(10);

                  let vFileTyp = ' (' & '$(vSourceFileFormat)' & ', Codepage is ' & $(vSourceCodePage) & ', ' & '$(vSourceLabels)' & ', delimiter is ' & '$(vSourceDelimiter)' & ');';

                  let vFrom = ' From ' & '$(vSourcePath)' & '$(vSourceFile)' & '$(vFileTyp)';

                  let vLoadStatement = '$(vTableName)' & ':' & chr(10) & replace('$(vLoadFields)', '|', chr(39)) & '$(vFrom)';

                 

                  let vLoadStart = $(eLoadStart);

                  $(vLoadStatement)

                  store $(vTableName) into $(vTargetComplete);

                  $(Include=$(pScript)Load\Include_LoadLogging.txt);

                  drop tables    $(vTableName);

                  let vTableCounter = $(vTableCounter) - 1; // Minusfaktor entspricht der Anzahl der gelöschten Tabellen im drop-statement

                     

              next

               

              /***************************************************************************************************************************************************************/

               

              - Marcus