topic Re: Facing a problem for Concatenation in for loop in QlikView

Facing a problem for Concatenation in for loop

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Facing a problem for Concatenation in for loop

MK_QSL — Mon, 26 Jun 2017 09:48:42 GMT

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

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

Re: Facing a problem for Concatenation in for loop

niclaz79 — Mon, 26 Jun 2017 09:57:35 GMT

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.

Re: Facing a problem for Concatenation in for loop

MK_QSL — Mon, 26 Jun 2017 10:01:33 GMT

Yeah.. good point...

or use as below

for vRowCount=1 to $(vNoOfRows)

Re: Facing a problem for Concatenation in for loop

ananyaghosh — Mon, 26 Jun 2017 11:42:45 GMT

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);