Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have an incredibly frustrating issue with my script. I'm trying to use a for each statement with a where clause, and it doesn't matter how i write it, I'm getting 'field not found - LINL1'. If I hard code this information into the script, it works fine.
I'm assuming I'm doing something stupid... If anyone can help shed any light, it'd be greatly appreciated....
Here is my script. FlowRate works fine, but Results doesn't;
For Each vSchema in '5', '2';
FlowRate:
LOAD @1 as DateTime,
@2 as [Flow Rate],
$(vSchema) as Pump
FROM
$(vInputPath)Environmental\Renewi - Pump Station $(vSchema)*.csv
(txt, codepage is 28591, no labels, delimiter is ',', msq, header is 1 lines);
next
store * from FlowRate into $(vOutputPath)Flow Rates.qvd (qvd);
drop table FlowRate;
For each vSample in 'LINL1','LINL2','LINSD';
Results:
LOAD [Parameter:],
[Date:],
[Operator ID:],
[Sample ID:],
[Sample ID:]&'_'&Program AS %SampleKey,
[Sample Number],
Program,
Result,
Unit,
Name,
Error,
Wavelength,
Value,
Unit1,
Dilution
FROM
$(vInputPath)Environmental\Lingerton*.xlsm
(ooxml, embedded labels, table is Raw_data)
where Program = 302 and upper([Sample ID:])= $(vSample)
;
next
store * from Results into $(vOutputPath)Ammonia Results.qvd (qvd);
drop table Results;
Change the script to this and it works???
//For each vSample in 'LINL1','LINL2','LINSD';
Results:
LOAD [Parameter:],
[Date:],
[Operator ID:],
[Sample ID:],
[Sample ID:]&'_'&Program AS %SampleKey,
[Sample Number],
Program,
Result,
Unit,
Name,
Error,
Wavelength,
Value,
Unit1,
Dilution
FROM
$(vInputPath)Environmental\Lingerton*.xlsm
(ooxml, embedded labels, table is Raw_data)
where Program = 302 and [Sample ID:]= 'LINL1'
;
//Next
store * from Results into $(vOutputPath)Ammonia Results.qvd (qvd);
drop table Results;
Thanks in advance
the where statement should be:
where Program = 302 and upper([Sample ID:])= '$(vSample)'
so add the quotes
the where statement should be:
where Program = 302 and upper([Sample ID:])= '$(vSample)'
so add the quotes
Thanks Jochem_zw. I knew it would be something silly I was missing (though could have sworn I'd tried that already!)