Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using the below code:
[Extract]:
LOAD TableName,
ExtractName,
Extract
FROM
(ooxml, embedded labels, table is GEOGRAPHY)
where Extract='Y'
;
let vNoOfRows = NoOfRows('Extract');
trace hi.... $(vNoOfRows);
//exit script;
for vRowCount=0 to $(vNoOfRows)
let vExtractName = peek('ExtractName', $(vRowCount), 'Extract'); // $(ExtractName);
Let vTotalColName = IF(len('$(vTotalColName)')>0, '$(vTotalColName)' & ',') & '$(vExtractName)';
next vRowCount;
let vColName = chr(39) & Replace('$(vTotalColName)', ',', chr(39) & ',' & chr(39)) & chr(39);
trace $(vColName);
output for $(vColName):
'GEO_KEY','CNTRY_ID','SC_CNTRY_ID','CNTRY_NAME','CNTRY_ISO_CODE_NUMERIC','CNTRY_ISO_CODE_2CHAR','CNTRY_ISO_CODE_3CHAR','STATE_ID','STATE_CD','STATE_NAME','REGN_ID','REGN_CD','REGN_NAME','TIME_ZONE','UPD_TS','','GEO_KEY','CNTRY_ID','SC_CNTRY_ID','CNTRY_NAME','CNTRY_ISO_CODE_NUMERIC','CNTRY_ISO_CODE_2CHAR','CNTRY_ISO_CODE_3CHAR','STATE_ID','STATE_CD','STATE_NAME','REGN_ID',
'REGN_CD','REGN_NAME','TIME_ZONE','UPD_TS',''
You can see that I got ,'' at the end of the string.
So how can I remove that?
Thanks,
Sandip
Hi, have you tried using $(vNoOfRows)-1? Rowindex starts at 0, so you would get for instance a 10 in your variable where the row index are between 0 and 9. So it appears your loop is running one time too many.
Try using below instead of len('$(vTotalColName)')
Len(Trim('$(vTotalColName)'))
Hi, have you tried using $(vNoOfRows)-1? Rowindex starts at 0, so you would get for instance a 10 in your variable where the row index are between 0 and 9. So it appears your loop is running one time too many.
Yeah.. good point...
or use as below
for vRowCount=1 to $(vNoOfRows)
Hi,
Below is the correct code to use:
[Extract]:
LOAD TableName,
ExtractName,
Extract
FROM
(
where Extract='Y'
;
let vNoOfRows = NoOfRows('Extract')-1; //RangeMax(NoOfRows('Extract'),0)-1;
//trace hi.... $(vNoOfRows);
Let vTotalColName = '';
//exit script;
for vRowCount=0 to $(vNoOfRows)
let vExtractName = peek('ExtractName', $(vRowCount), 'Extract');
Let vTotalColName = IF(len('$(vTotalColName)')>0, '$(vTotalColName)' & ',') & '$(vExtractName)';
next vRowCount;
let vColName = chr(39) & Replace('$(vTotalColName)', ',', chr(39) & ',' & chr(39)) & chr(39);
trace $(vColName);