variable not reading getting error

[nvijay5757]

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

[henrikalmen]

What is the error message you are getting?

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

[anat]

can you try by placing single quotes ,like below:

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

[henrikalmen]

I should have seen that as well. 🙂

[nvijay5757]

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

[nvijay5757]

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

[henrikalmen]

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.