Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

pschmidt1973
Not applicable

Entire load statement in a Variable

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

2 Replies
marcus_sommer
Not applicable

Re: Entire load statement in a Variable

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

marcus_sommer
Not applicable

Re: Entire load statement in a Variable

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