How to get a list of fields in load script?

[#1]

Hi community,

I am sure there is a way, but can someone let me know if I would like to get a list of field name of a table in a load script, how would l do that? For example, I have a resident table TABLE_A, and I would like to query all of its fields, and store it in another resident table.

Thank you.

[Anonymous]

Why do you want it at script level???

Why not you use system fields>>> $table , $ field provides such info.....

[#3]

Because I need to use that information for calculations during the load. So, I take it, there is no straight forward way to do this?

[#4]

Hi,

No, I don't mean crosstable functionality.

You can try the below:

1. Create a Temp table with all the columns from Table_A as:

Temp:

Load * resident Table_A;

2. Then for the transpose :

Actual:

Load

COl1 as Metric,

--------

Resident Temp;

Concatenate

Load

COL2 as Metric,

---------

and so on for all the columns (Col1 to COL50).

3. In the front end, create a table and use "Metric" as the dimension.

4. Also, you can add

'1' as [Sort Order],

'2' as [Sort Order] etc. with every concatenation and then sort this column as Ascending/Descending in the front end to get the COL1, COL2 values in a required order.

This Might not be an ideal way for huge number of columns but just a way to do it

Hope this helps!

[#5]

Hi Shyamal,

thank you for your suggestion, but I was hoping not to go that route as my table has over 600+ columns, and I need to be able to pick and choose columns for calculation dynamically.

Anyways, thank you everyone for the prompt responses.

p.s. I thought QV might have a function similar to "PROC CONTENTS" in SAS.

[rwunderlich · Accepted solution]

This will work for a table named "Customers"

FOR i = 1 to NoOfFields('Customers')

Fields:

LOAD

  FieldName($(i),'Customers') as FieldName

AutoGenerate 1

;

NEXT i

-Rob

[#7]

Perfect, thank you Rob.

[yink-hpv]

Hi master Rob: how about i would like to have all field name for all tables in the loading script? thanks for  sharing.

[shilpan]

for j=1 to NoOfTables()
FOR i = 1 to NoOfFields(TableName($(j)))

Fields:

LOAD
TableName($(j)) as TableName,
FieldName($(i),TableName($(j))) as FieldName

AutoGenerate 1

;

NEXT i
next j

[rwunderlich]

Perfect Shilpan. Except...the table array is 0 based so you'll want the first line to be:

for j=0 to NoOfTables()-1

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[shilpan]

Thanks Rob!