topic Re: variable not reading getting error in Qlik Automate

variable not reading getting error

nvijay5757 — Fri, 03 Mar 2023 10:58:45 GMT

Hi i am getting error in this code. i.e RCM_Accel not found

$(new_col) AS [$(new_col)], in this i am getting error. help me.

// Set the path to the directory containing input Excel files
LET path = '\\dc18201bcf1.hosts.cloud.ford.com\q00000\Enterprise_Analytics\Projects\WIP\Sensor_Calibration\Input_files\Classification\P702\test\';

// Find all Excel files in the directory
FOR EACH FileExtension IN 'xlsx'
FOR EACH File IN FileList('$(path)*.' & FileExtension)

// Load the data from the current file
data:
LOAD *
FROM [$(File)]
(ooxml, embedded labels, table is Sheet1);

// Set the threshold value
LET threshold = 3;

// Set the name of the column to transform
LET column = 'T RCM C1 HX REF 032 BTWN SEATS RCM FRONT X SM LONG WIDEBAND';

// Create new column names
LET new_col = 'RCM_Accel';
LET new_time_col = 'Wakeup_time';

// Transform the data
data:
NOCONCATENATE
LOAD
*,
$(new_col) AS [$(new_col)],
'Time' - FirstSortedValue('Time', If([$(new_col)] < 0, 1, 0)) AS [$(new_time_col)]
RESIDENT data
WHERE NOT IsNull([$(column)])
;

// Add the threshold to the RCM_Accel column
data:
NOCONCATENATE
LOAD
*,
[$(column)] + $(threshold) AS [$(new_col)]
RESIDENT data
;

// Save the modified data to an output Excel file
LET output_path = Replace('$(File)', '$(path)', '\\dc18201bcf1.hosts.cloud.ford.com\q00000\Enterprise_Analytics\Projects\WIP\Sensor_Calibration\Input_files\Classification\P702\output');
Store data INTO [$(output_path)]

DROP TABLE data;

NEXT File
NEXT FileExtension

Re: variable not reading getting error

henrikalmen — Fri, 03 Mar 2023 11:20:03 GMT

What is the error message you are getting?

You could possibly try SET instead of LET when declaring the variable.

Re: variable not reading getting error

anat — Fri, 03 Mar 2023 14:37:44 GMT

can you try by placing single quotes ,like below:

'$(new_col)' AS [$(new_col)]

Re: variable not reading getting error

henrikalmen — Fri, 03 Mar 2023 15:23:44 GMT

I should have seen that as well. 🙂

Re: variable not reading getting error

nvijay5757 — Sat, 04 Mar 2023 03:18:56 GMT

i am getting error message. variable not recognizing. any other possible ways

Re: variable not reading getting error

nvijay5757 — Sat, 04 Mar 2023 03:20:54 GMT

yes i have tried both set and let, and single quote also. variable not recognizing.

Re: variable not reading getting error

henrikalmen — Sat, 04 Mar 2023 09:22:50 GMT

The variable is doing what it is instructed to do.

The error message is telling you that there is no field named RCM_Accel in the table “data”. The script is trying to load data from that field and store it into the fieldname RCM_Accel_tmp - but since RCM_Accel doesn’t exist you get the error.