Is it possible to update a Data Load Script based on an external Excel file?

[spjuza]

I have a column of comment text data that I am applying a text hierarchy to code the data based on key themes.  Based on the app use cases, I need to create several new columns based on that information.  A mock table is below

Comment Text | Theme 1| Theme 2|....Theme n| Concatenated Themes

Comment Text	Example Theme 1	Example Theme 2	...	Exmple Theme n	Concatenated Themes
Text with various themes	1	0		0	1|0|0
Different Text with other themes	0	1		1	0|1|0

The issue that I'm running into is there are probably a hundred themes that I'm wanting to tag (and new tags each month).  However, the current way that I'm handling this is through a very long data load script.  For example, one line is:

If (WildMatch([Comment Text],'*Theme 1*'),1, 0) AS [Example Theme 1]) ...

Repeated for each theme.  

Then to create the final column, I have another block of code that identifies each column and concatenates it using 

[Example Theme 1]&' | ' &
[Example Theme 2]&' | ' &

[Example Theme  n] as [Concatenated Themes]

The main issue that I don't like with this is it becomes very unsustainable because there are so many tags, and some of them have multiple themes they are looking for in an individual column (think synonyms).  

I have an Excel with all the themes and keywords that I'm focused on.  Is there a way to automatically create and update the data load script based on the Excel values?

Thanks!

[rubenmarin]

Hi, I dind't fully understand your requirement.

In some projects I made a script that reads a sheet form excel, and with a FOR bucle that reads each row of the excel adding to a vscript variable the conditions of this row, as a simple example:

// Read meause definitions from excel
tmpExcelMeasures:
NoConcatenate
LOAD 
     IdMeasure, 
     Measure
From 
	[$(vConfig)Config_Metadata.xlsx]
	(ooxml, embedded labels, table is [Manual])
;

LET NumRowsManual = NoOfRows('tmpExcelMeasures')-1;

// Generate script
LET vScript = 'IdDate';
FOR i=0 to $(NumRowsManual)
	LET vIdMeasure 	= Peek('IdMeasure',$(i),'tmpExcelMeasures');
	LET vMeasure 	= Peek('Measure',$(i),'tmpExcelMeasures');
	
	LET vScript = '$(vScript), 
  		If(IdMeasure=$(vIdMeasure), Amount) as  $(vMeasure)';
NEXT;

DROP table tmpExcelMeasures;

// Load script
FinalTable:
LOAD 
	 $(vScript)
Resident
	tmpDataTable
;

This is just a simplification of the script that uses a bucle to generate the script in a variable and at the end uses that variable in the load.

Maybe you can use the crosstable sentence to unpivot the themes: https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-c...