Auto generate store location using variable

[Sebastian_Dec]

Hi, how can I generate store location from variable in sense data load editor?

I read this article, but I don't find solution: https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularSt...

My code:

LIB CONNECT TO [HD];

[Segmentacja]:
LOAD 
	Date(hid_data) as Data, 
    Month(hid_data) as Miesiac,
    Year(hid_data) as Rok,
	hid_godzina, 
	hid_sklep, 
	hid_segment, 
	nr_sklep, 
	rabat, 
	rabat_2, 
	data_tech, 
	nr_karta_lojal, 
	obrot_brutto
FROM [lib://QlikContainers\Prod\Paragony\QVD\HD\Segementacja_2024_07.qvd]
(qvd)
;

LET vMaxYear = Year(Max(hid_data)); // Gets the maximum year
LET vMaxMonth = Num(Month(Max(hid_data)), '00'); // Gets the maximum month in two-digit format

// Creating a filename with maximum year and month
LET vFileName = 'TEST_Segementacja_' & vMaxYear & '_' & vMaxMonth & '.qvd';
LET vStore = '[lib://QlikContainers\Prod\Paragony\QVD\HD\'&vFileName&']';
// LET vStore = '[lib://D\HD\'&vFileName;

//store [Segmentacja] into [lib://QlikContainers\Prod\Paragony\QVD\HD\$vFileName] (qvd);
store [Segmentacja] into vStore (qvd);

Drop Table Segmentacja;

I have this error:

[Clement15]

Hello,

Can you try with this syntax

// Creating a filename with maximum year and month
LET vFileName = 'TEST_Segementacja_' & $(vMaxYear) & '_' & $(vMaxMonth) & '.qvd';
LET vStore = '[lib://QlikContainers\Prod\Paragony\QVD\HD\'& $(vFileName) &']';
// LET vStore = '[lib://D\HD\'& $(vFileName);

//store [Segmentacja] into [lib://QlikContainers\Prod\Paragony\QVD\HD\$(vFileName)] (qvd);
store [Segmentacja] into $(vStore) (qvd);

[Sebastian_Dec]

@Clement15 thx, for help, I have progress because I have saved file via this code:

store [Segmentacja] into $(vStore) (qvd);

I have problem with:

LET vFileName = 'TEST_Segementacja_' & $(vMaxYear) & '_' & $(vMaxMonth) & '.qvd';
LET vStore = '[lib://QlikContainers\Prod\Paragony\QVD\HD\'& $(vFileName) &']';

This code dosn't work and still I have some errors:

For code:

LET vFileName = 'TEST_ M Segementacja_' & $(vMaxYear) & '_' & $(vMaxMonth) & '.qvd';
LET vStore = '[lib://QlikContainers\Prod\Paragony\QVD\HD\'& $(vFileName) &']';

For code:

LET vFileName = 'TEST_Segementacja_' & vMaxYear & '_' & vMaxMonth & '.qvd';
LET vStore = '[lib://QlikContainers\Prod\Paragony\QVD\HD\'& $(vFileName) &']';

[diegozecchini]

Hi
To resolve the error with generating a dynamic store location using variables in Qlik Sense Data Load Editor, ensure the syntax for variable expansion and concatenation is correct. In Qlik Sense, LET is used to assign string values, and variable expansion is done using $().

Here’s the approach I suggest

Copia codice
// Get the maximum year and month
LET vMaxYear = Year(Max(hid_data)); // Maximum year
LET vMaxMonth = Num(Month(Max(hid_data)), '00'); // Two-digit maximum month

// Create the filename with dynamic year and month
LET vFileName = 'TEST_Segementacja_' & $(vMaxYear) & '_' & $(vMaxMonth) & '.qvd';

// Create the store path dynamically
LET vStore = '[lib://QlikContainers/Prod/Paragony/QVD/HD/' & $(vFileName) & ']';

// Store the table into the dynamically generated file path
STORE [Segmentacja] INTO $(vStore) (qvd);

// Drop the table to free up memory
DROP TABLE Segmentacja;

Variable Expansion: Use $() for variable expansion inside LET. For example, $(vMaxYear) ensures the value of vMaxYear is correctly inserted into the string.
String Concatenation: Use & to concatenate strings and variables.
File Path: Ensure the generated file path (vStore) resolves to a valid location.

[Clement15 · Accepted solution]

Hello,

And with this?

Temp:
LOAD
    max(Data) as maxDate
RESIDENT [Segmentacja]
Order by Data;

LET vMaxYear = Year(Peek('maxDate',-1,'Temp')); // Gets the maximum year
LET vMaxMonth = Num(Month(Peek('maxDate',-1,'Temp')), '00'); // Gets the maximum month in two-digit format


// Creating a filename with maximum year and month
LET vFileName = 'TEST_Segementacja_' & $(vMaxYear) & '_' & $(vMaxMonth) & '.qvd';
LET vStore = '[lib://QlikContainers\Prod\Paragony\QVD\HD\'& $(vFileName) &']';
// LET vStore = '[lib://D\HD\'& $(vFileName);

//store [Segmentacja] into [lib://QlikContainers\Prod\Paragony\QVD\HD\$(vFileName)] (qvd);
store [Segmentacja] into $(vStore) (qvd);

[Sebastian_Dec · Accepted solution]

@diegozecchini thanks for the advice, unfortunately it was the same code as we provided earlier.

@Clement15 I figured it out and now I know where the problem was, I don't know if your code works without problems for you, but it kept showing an error if variables were created based on $(), only store should have been left as $(vStore). But your code helped a lot, thank you very much.

Below is the correct solution:

[Segmentacja]:
LOAD 
	Date(hid_data) as Data, 
    Month(hid_data) as Month,
    Year(hid_data) as Year,
	hid_shop, 
	hid_segment,  
	sales_gross
FROM [lib://QlikContainers/Prod/Paragony/QVD/HD/Segementacja_2024_07.qvd]
(qvd)
;

Temp:
LOAD
    max(Data) as maxDate
RESIDENT [Segmentacja]
Order by Data;

LET vMaxDate = max(maxDate);
LET vMaxYear = Year(Peek('maxDate',-1,'Temp')); // Gets the maximum year
LET vMaxMonth = Num(Month(Peek('maxDate',-1,'Temp')), '00'); // Gets the maximum month in two-digit format

// Create dynamic file name
LET vFileName = 'TEST_Segementacja_' & vMaxYear & '_' & vMaxMonth & '.qvd';

// Create dynamic path to save
LET vStore = '[lib://QlikContainers/Prod/Paragony/QVD/HD/' & vFileName & ']';

// Save table to QVD file
STORE [Segmentacja] INTO $(vStore) (qvd);

// Delete table
DROP TABLE Segmentacja;