Rename field in loop store statement

Hannah_M — Wed, 22 Jan 2025 19:25:34 GMT

I have a folder that contains several monthly .xlsx files. Most of these files contain a field called "Grade Category". In the newer months, this field is renamed to "Career Band/Level Category". I am trying to create a process to loop through each of these files and rename the field if needed and store into a qvd. The 2 processes are broken out below. I've also included my script. This script works fine for all of the files that contain "Grade Category". Once it gets to a file where the field is "Career Band/Level Category" I am getting an error stating 'FinalTable' is not found.

Any suggestions on what I'm doing wrong? Thanks in advance!

1. If the field in the file is called "Grade Category", rename it to "Career Band/Level Category" and then store it into a qvd.

2. If the field in the file is called "Career Band/Level Category", no renaming is necessary and the file can be stored into a qvd.

My Script:

// Define the source folder containing the raw files and the QVD target folder
LET vSourceFolder = 'lib:/.../';
LET vTargetFolder = 'lib://.../';

// Get a list of the files in the source folder
FOR Each vFile in FileList('$(vSourceFolder)*Global Audit Reports Active *.xlsx')
// Extract the file name without the extension
LET vFileName = SubField(vFile, '/', -1);
LET vFileBaseName = SubField(vFileName, '.', 1); // Remove the extension

// Construct the QVD file name
LET vQvdFileName = '$(vTargetFolder)' & '$(vFileBaseName).qvd';

// Debugging: Trace the generated QVD file name
TRACE Checking QVD file: $(vQvdFileName);

// Check if the QVD already exists
IF NOT IsNull(FileSize('$(vQvdFileName)')) THEN
// If FileSize returns a number, that means the file exists
TRACE QVD [$(vQvdFileName)] already exists, skipping loading of [$(vFile)];
ELSE
// Load the data from the Excel file into a temporary table
TempTable:
LOAD
*
FROM [$(vFile)]
(ooxml, embedded labels, table is [RPT033]);

// Check for the existence of the Career Band/Level Category field using FieldIndex
LET vFieldExists = FieldNumber('Career Band/Level Category', 'TempTable');
TRACE Checking vFieldExists: $(vFieldExists);

// Create the final table based on the field existence
IF vFieldExists > 0 THEN
FinalTable:
LOAD
*
RESIDENT TempTable;
ELSE
FinalTable:
LOAD
*,
"Grade Category" AS "Career Band/Level Category"
RESIDENT TempTable;
Drop Field "Grade Category" from FinalTable;
ENDIF;

// Store the data from the final table into QVD
STORE FinalTable INTO [$(vQvdFileName)];

// Drop the temporary tables to free up memory
DROP TABLE TempTable;
DROP TABLE FinalTable;
ENDIF
NEXT vFile;

Log:

QVD [lib://.../Global Audit Reports Active 2024-11.qvd] already exists, skipping loading of [lib://.../Global Audit Reports Active 2024-11.xlsx] Checking QVD file: lib://.../Global Audit Reports Active 2024-12.qvd

TempTable << RPT033

Lines fetched: 40,826 Checking vFieldExists: 18 TempTable << TempTable

Lines fetched: 81,652

The following error occurred:

Table 'FinalTable' not found

The error occurred here:

STORE FinalTable INTO [lib://.../Global Audit Reports Active 2024-12.qvd]

Data has not been loaded. Please correct the error and try loading again.

Re: Rename field in loop store statement

maxgro — Wed, 22 Jan 2025 21:16:52 GMT

may be you need a NOCONCATENATE when you load the FinalTable from TempTable

Re: Rename field in loop store statement

rwunderlich — Thu, 23 Jan 2025 19:20:29 GMT

I think you may be overthinking it. I would think using just an ALIAS statement would work. The Alias statement will automatically rename the field if it exists.

Alias "Career Band/Level Category" as "Grade Category";
LOAD * From lib://source/*.xlsx;

You can adapt this to a For loop if you actually need to read and store each file -- but I was guessing maybe you were just doing that for the field rename.

See https://qlikviewcookbook.com/2018/12/loading-varying-column-names/

-Rob

topic Rename field in loop store statement in App Development

Rename field in loop store statement

Re: Rename field in loop store statement

Re: Rename field in loop store statement