Load script - fields calc for missing fields

[seWork]

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

[ogautier62]

Hi,

you can use functions TableName and FieldName with Nooftables, NoOfFields

to retreive fields and table in your script

regards

[seWork]

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]

yes, it should work  :

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

Load A,

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

Resident Base table

group by A;

[seWork]

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]

ok,

try :

For i = 2 to vTable;

Load A,

Avg(FieldName(i,Base table))

[ogautier62 · Accepted solution]

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]

Thank you,

I run it successfully!