Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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