topic Re: Create a table of column names from another table in QlikView

Create a table of column names from another table

Anonymous — Mon, 15 Jan 2018 08:18:54 GMT

I have loaded a table ('FACT') that contains 234 columns of data.

I want to create a new table ('FIELDS') that has 1 column only, containing the names of those 234 columns, ie. a table with 1 column containing 234 values.

Can this be done? If so, how?

Cheers

Re: Create a table of column names from another table

Anil_Babu_Samineni — Tue, 16 Jan 2018 02:08:13 GMT

Not sure, I understand your issue. Can you explain little more or try this way for a while?

Table:

Mapping Load * From FACT;

FIELDS:

Load *, ApplyMap('Table', 'Primary Field', 'NA') as Primary

From Table1;

Re: Create a table of column names from another table

justinphamvn — Tue, 16 Jan 2018 03:24:17 GMT

Hi Hammond,

I think you should used Cross Table.

Link : Working with crosstables in the data load script ‒ Qlik Sense

Hope this help

Justin.

Re: Create a table of column names from another table

vishsaggi — Tue, 16 Jan 2018 04:42:56 GMT

Try this? Reference taken from here: How to get a list of fields in load script?

FACT:

LOAD yourcolumns,

FROM yoursource;

Fields:

FOR i = 1 to NoOfFields('FACT')

Fields:

LOAD

FieldName($(i),'FACT') as FieldNames

AutoGenerate NoOfFields('FACT')

;

NEXT i

Re: Create a table of column names from another table

Anonymous — Tue, 16 Jan 2018 13:18:56 GMT

Thanks for the suggestions, but none of them worked.

I eventually solved it by constructing a string of field names delimited by a carriage return (ASCII code 13).

The code I used was:

set vList=;

set vDelimiter=CHR(13);

for i = 1 to NoOfFields('FACT')

let vFieldName = FieldName($(i), 'FACT');

let vList = '$(vList)' & $(vDelimiter) & '$(vFieldName)';

next i

TMP:

load * inline [

COLUMN_NAME

$(vList)

];

COLUMNS:

noconcatenate

load COLUMN_NAME

resident TMP

order by COLUMN_NAME asc;

drop table TMP;

Re: Create a table of column names from another table

vishsaggi — Tue, 16 Jan 2018 16:29:48 GMT

Glad you figured it out. However, Your initial question was very generic. Not knowing your full requirement we could not produce exact solution hence provided possible solutions. Please Close the thread marking correct/helpful responses accordingly.

Re: Create a table of column names from another table

KeaneGleeson — Wed, 12 Oct 2022 15:33:09 GMT

I like this recursive way of doing this with a variable referencing itself in the for loop. I would have probably looped through concatenating onto a table with each field name, but this seems much more efficient. Thanks!