Entire load statement in a Variable

[pschmidt1973]

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:

Source	TargetName	SourceField	TargetField	StageType	SortOrder	ObjectType
IG	DimCashier	Autonumber(%CashierKey)	%CashierKey	Presentation	1	Dim
IG	DimCashier	Cashier ID	Cashier ID	Presentation	2	Dim
IG	DimCashier	Cashier Name	Cashier Name	Presentation	3	Dim

Best, Paul

[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

[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