Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi,
you can use functions TableName and FieldName with Nooftables, NoOfFields
to retreive fields and table in your script
regards
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
yes, it should work :
fname = FieldName($(i),'Base table');
Load A,
Avg( $(fname) ) as avg$(fname)
Resident Base table
group by A;
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
ok,
try :
For i = 2 to vTable;
Load A,
Avg(FieldName(i,Base table))
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;
Thank you,
I run it successfully!