topic Re: Loop for column names in App Development

Loop for column names

HTaher — Wed, 27 Sep 2023 07:55:02 GMT

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

Re: Loop for column names

maxgro — Wed, 27 Sep 2023 11:08:17 GMT

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

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

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

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

$(vLoad)

;

Re: Loop for column names

HTaher — Wed, 27 Sep 2023 12:54:19 GMT

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

Re: Loop for column names

maxgro — Wed, 27 Sep 2023 13:09:00 GMT

concatenate columns?

col1, col2, col3

1,2,3

do you mean

newcol

123

Re: Loop for column names

HTaher — Wed, 27 Sep 2023 13:12:33 GMT

Yes, newcol

Re: Loop for column names

HTaher — Wed, 27 Sep 2023 13:19:49 GMT

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

Re: Loop for column names

maxgro — Wed, 27 Sep 2023 14:58:14 GMT

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;

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

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

DROP TABLE Y;

Re: Loop for column names

HTaher — Wed, 27 Sep 2023 15:05:55 GMT

Thank you very much for your help. Works wonderfully