Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create a table of column names from another table

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;

View solution in original post

6 Replies
Anil_Babu_Samineni

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
justinphamvn
Creator II
Creator II

Hi Hammond,

I think you should used Cross Table.

Hope this help

Justin.

vishsaggi
Champion III
Champion III

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

Anonymous
Not applicable
Author

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;

vishsaggi
Champion III
Champion III

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.

KeaneGleeson
Partner - Contributor III
Partner - Contributor III

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!