Facing a problem for Concatenation in for loop

[ananyaghosh]

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

[MK_QSL]

Try using below instead of len('$(vTotalColName)')

Len(Trim('$(vTotalColName)'))

[niclaz79 · Accepted solution]

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.

[MK_QSL]

Yeah.. good point...

or use as below

for vRowCount=1 to $(vNoOfRows)

[ananyaghosh]

Hi,

Below is the correct code to use:

[Extract]:
LOAD TableName, 
ExtractName, 
Extract  
FROM

(ooxml, embedded labels, table is CHANNEL)
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);