topic How to get a list of fields in load script? in QlikView

How to get a list of fields in load script?

Wed, 12 Mar 2014 10:06:37 GMT

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.

Re: How to get a list of fields in load script?

tresB — Wed, 12 Mar 2014 10:09:22 GMT

Like this, using '*' ?

Tab2:

Load * resident TABLE_A;

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:12:32 GMT

Table1:

load * from Table1;

Table2:

load * resident Table1;

store Table2 into D:\Table2.qvd (qvd);

Drop table Table1;

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:13:36 GMT

I think this just loads Table_A into Tab2. What I need is, suppose TABLE_A has 50 columns ('COL1',...,'COL50'), I would like to create a table with 1 column and 50 rows where row1 = 'COL1', row2 = 'COL2' and so forth.

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:14:53 GMT

try to use Crosstable concept

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:17:32 GMT

I used crosstable which somewhat works, but just wondering if there is a function or another way to get the list of fields.

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:18:59 GMT

try according to this

Crosstable(NewCol, value)

load

Rowno() as key,

Col1,

Col2,

Col3,

Col50

Resident tablename;

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:19:43 GMT

Have you tried transposing the fields?

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:28:21 GMT

Hi there,

I am sure what you mean by "transposing the fields"? Are you referring to Crosstable functionality?

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:31:22 GMT

Hi Shyamal,

sorry, I meant "not sure". Typo.

Re: How to get a list of fields in load script?

Anonymous — Wed, 12 Mar 2014 10:34:30 GMT

Why do you want it at script level???

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

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 10:39:03 GMT

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?

Re: How to get a list of fields in load script?

Wed, 12 Mar 2014 11:10:11 GMT

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!

Re: How to get a list of fields in load script?

Thu, 13 Mar 2014 00:57:58 GMT

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.

Re: How to get a list of fields in load script?

rwunderlich — Thu, 13 Mar 2014 04:11:05 GMT

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

Re: How to get a list of fields in load script?

Thu, 13 Mar 2014 06:54:23 GMT

Perfect, thank you Rob.

Re: How to get a list of fields in load script?

yink-hpv — Wed, 21 Feb 2018 00:32:30 GMT

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

Re: How to get a list of fields in load script?

shilpan — Wed, 21 Feb 2018 00:47:28 GMT

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

Re: How to get a list of fields in load script?

rwunderlich — Wed, 21 Feb 2018 22:09:09 GMT

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

Re: How to get a list of fields in load script?

shilpan — Wed, 21 Feb 2018 23:01:03 GMT

Thanks Rob!