Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Why do you want it at script level???
Why not you use system fields>>> $table , $ field provides such info.....
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?
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!
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.
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
Perfect, thank you Rob.
Hi master Rob: how about i would like to have all field name for all tables in the loading script? thanks for sharing.
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
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
Thanks Rob!