topic Re: Help with string building in QlikView

Help with string building

alexis — Thu, 04 Jul 2013 12:08:44 GMT

I have an Excel spreadsheet that contains a column of numbers (e.g. 8418, 8423, 8424,8424, 8447 ....)

I want to read this list in order to create a string of the order:

'8418*', '8423*', '8424*','8424*','8447*'

i.e. each number will have a * after it and each set enclosed in single quotes as shown and separated by a comma.

I made a start as follows:

Codes:
LOAD chr(39) & A & '*' & chr(39) As Value
FROM
CommodityCodes.xlsx
(ooxml, no labels, table is Sheet1);

How do I build a string from the table and lose the last comma?

Any ideas would be very welcome.

Regards

Alexis

Re: Help with string building

Gysbert_Wassenaar — Thu, 04 Jul 2013 12:19:53 GMT

Try

CodeList: LOAD concat(chr(39) & A & '*' & chr(39), ',') As ValueList FROM CommodityCodes.xlsx (ooxml, no labels, table is Sheet1);

Re: Help with string building

alexandros17 — Thu, 04 Jul 2013 12:25:16 GMT

let

yyy='';

for i = 0 to NoOfRows('Codes')-1

xxx= Peek('Value', $(i), 'Codes') ;

yyy = yyy & ',' & xxx;

NEXT

let zzz = mid(yyy,2);

Re: Help with string building

alexis — Thu, 04 Jul 2013 12:34:06 GMT

Thank you Gysbert.

The complete solution is:

CodeList:

LOAD concat(chr(39) & A & '*' & chr(39), ',') As ValueList

FROM

CommodityCodes.xlsx

(ooxml, no labels, table is Sheet1);

Let vSearch = Peek('ValueList',0,'CodeList');

Thanks

Alexis