Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
seWork
Contributor III
Contributor III

Load script - fields calc for missing fields

Hi all,

Please advise me

I'm trying to create a resident table that will calculate Num/Int fields avg per Category but I do not know which fields will be existing in the base table in advance

For example

//Base table could include fields A-E

Base table:

Load

A,//String

B,//Num

C,//Num

D,//String

From Base.qvd;

Calc table:

Load

A,//String

Avg(B)//Num

Avg(C)//Num

Avg(E)//Num

resident

group by A;

Thanks

1 Solution

Accepted Solutions
ogautier62
Specialist II
Specialist II

Hi,

here is it, I had time to test :

let vTable = NoOfFields('test');

avgtable :

load distinct group  resident test;

for i = 2 to vTable

    let fname = FieldName(i,'test');

    inner join (avgtable)

        Load group, Avg( $(fname) ) as avg$(fname)

        Resident test group by group;

next i;

View solution in original post

7 Replies
ogautier62
Specialist II
Specialist II

Hi,

you can use functions TableName and FieldName with Nooftables, NoOfFields

to retreive fields and table in your script

regards

seWork
Contributor III
Contributor III
Author

Hi Olivier,

Thank you,

If I understood you correct so you are suggesting to use

loop function?

I tried to use the following without success

For i = 2 to NoOfFields(Base table);

Load A,

Avg(FieldName($(i),Base table))

Resident Base table

group by A;

Next

ogautier62
Specialist II
Specialist II

yes, it should work  :

fname = FieldName($(i),'Base table');

Load A,

Avg( $(fname) ) as avg$(fname)

Resident Base table

group by A;

seWork
Contributor III
Contributor III
Author

Thank you very much

I succeed to run it

at the moment the issue is that I'm struggling with the exit from the loop

in my example, the for i statement is set "for i =2 to 5"

but the script tries to load AvgDataTemp6 even that it does not exist

my script is

BaseTable:

Load *

From BaseTable.qvd;

let vTable = NoOfFields('BaseTable');

For i = 2 to $(vTable);

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

AvgDataTemp$(i):

Load Category,

Avg($(vFieldName)) as $(vFieldName)

Resident BaseTable

group by Category;

Next

AvgData:

Load *

Resident AvgDataTemp$(i);

Left join //in order to create a table and to Concatenate it with the base table

AvgData:

Load *

Resident AvgDataTemp$(i);

Next

drop table AvgDataTemp$(i);

Next

ogautier62
Specialist II
Specialist II

ok,

try :

For i = 2 to vTable;

Load A,

Avg(FieldName(i,Base table))

ogautier62
Specialist II
Specialist II

Hi,

here is it, I had time to test :

let vTable = NoOfFields('test');

avgtable :

load distinct group  resident test;

for i = 2 to vTable

    let fname = FieldName(i,'test');

    inner join (avgtable)

        Load group, Avg( $(fname) ) as avg$(fname)

        Resident test group by group;

next i;

seWork
Contributor III
Contributor III
Author

Thank you,

I run it successfully!