Loop for column names

[HTaher]

Dear Qlik Community,

I have the following problem.

I have a table (QVD) with very many columns. e.g. ccPersonlist, ccPersonlist_M_1, ccPersonlist_M_2, ccPersonlist_M_3, ... ccPersonlist_M_40...

the number of columns can change.

I want to combine this many columns in one column.

I would combine the columns with Concatenate.

But for this I would like to build a loop that queries the possible number of columns before and includes all columns that start with ccPersonlist.

How can I do this? Do you have any ideas? How can I read all column names from the QVD beforehand and build them into the loop?

Thanks a lot

[maxgro]

maybe this; I used QlikView, change the read / store for Qlik Sense

// test data in a QVD (X.qvd)

X:

load

rowno() as ccPersonlist,

rowno() as ccPersonlist_M_1,

rowno() as Field1,

rowno() as ccPersonlist_M_2

AutoGenerate 100;

STORE X INTO X.qvd (qvd);

drop table X;

// read 1 row from the qvd X.qvd

Y: FIRST 1 LOAD * FROM X.qvd (qvd);

// build the load statement  

// read every column from Y table (for loop)

SET vLoad = 'LOAD ';

// vLOAD is LOAD  

for c = 1 to NoOfFields('Y')

        LET vField = FieldName($(c), 'Y');

        TRACE vField=$(vField);

        // add the column to vLoad if start with ccPersonlist

        IF WildMatch('$(vField)', '*ccPersonlist*') THEN 

                LET vLoad = '$(vLoad)'  & '$(vField) & ';

                TRACE $(vLoad);   

        ENDIF

NEXT 

DROP TABLE Y;

// vLOAD is LOAD ccPersonlist & ccPersonlist_M_1 & ccPersonlist_M_2 & 

LET vLoad = LEFT('$(vLoad)', LEN('$(vLoad)')-3) & ' as NewField ' &chr(10)& ' FROM X.qvd (qvd);';

TRACE $(vLoad);

// vLoad is LOAD ccPersonlist & ccPersonlist_M_1 & ccPersonlist_M_2 as NewField FROM X.qvd (qvd);

// and now, run the statement in the vLoad variable

Z:

$(vLoad)

;

[HTaher]

Thank you,

the script works.

But I would like to concatenate the columns.

When I change the script accordingly I get an error.

I have adapted your script as follows.

// read 1 row from the qvd X.qvd
Y: FIRST 1 LOAD * FROM [x.qvd] (qvd);

// build the load statement
// read every column from Y table (for loop)
SET vLoad = 'LOAD ';
// vLOAD is LOAD
for c = 1 to NoOfFields('Y')
LET vField = FieldName($(c), 'Y');
TRACE vField=$(vField);
// add the column to vLoad if start with ccPersonlist
IF WildMatch('$(vField)', '*ccPersonlist*') THEN
LET vLoad = '$(vLoad)' & '$(vField) as NewField FROM [X.qvd] (qvd); Concatenate';
TRACE $(vLoad);
ENDIF
NEXT
DROP TABLE Y;
// vLOAD is LOAD ccPersonlist & ccPersonlist_M_1 & ccPersonlist_M_2 &

LET vLoad = LEFT('$(vLoad)', LEN('$(vLoad)')-13);
TRACE $(vLoad);

I use Qlik Sens Saas.

I get the error because of ";" before the concatenate.
With & chr(59) & it didn't work either.

Do you know why this error occurs or how I can work around it?

Thanks a lot

[maxgro]

concatenate columns?

col1, col2, col3

1,2,3

do you mean

newcol

1

2

3

or

newcol

123

?

[HTaher]

Yes, newcol

1

2

3

[HTaher]

Like this only in as a loop.

Because the number of columns can change

X:
LOAD

CcPersonList AS NewField

FROM [x.qvd]
(qvd);

Concatenate

LOAD

CcPersonList_M_1 AS NewField

FROM [x.qvd]
(qvd);

Concatenate

LOAD

CcPersonList_M_2 AS NewField

FROM [x.qvd]
(qvd);

[maxgro · Accepted solution]

X:

load

rowno() as ccPersonlist,

rowno() as ccPersonlist_M_1,

rowno() as Field1,

rowno() as ccPersonlist_M_2

AutoGenerate 100;

STORE X INTO X.qvd (qvd);

drop table X;

Y: 

FIRST 1 LOAD * FROM X.qvd (qvd);

Z: 

LOAD 'x' as NewField AutoGenerate 0;

for c = 1 to NoOfFields('Y')

LET vField = FieldName($(c), 'Y');

TRACE vField=$(vField);

IF WildMatch('$(vField)', '*ccPersonlist*') THEN 

CONCATENATE (Z) LOAD $(vField) as NewField FROM X.qvd (qvd);

ENDIF

NEXT 

DROP TABLE Y;

[HTaher]

Thank you very much for your help. Works wonderfully