topic Re: Load script - fields calc for missing fields in QlikView

Load script - fields calc for missing fields

seWork — Tue, 05 Jun 2018 08:00:53 GMT

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

Re: Load script - fields calc for missing fields

ogautier62 — Tue, 05 Jun 2018 12:53:30 GMT

Hi,

you can use functions TableName and FieldName with Nooftables, NoOfFields

to retreive fields and table in your script

regards

Re: Load script - fields calc for missing fields

seWork — Tue, 05 Jun 2018 13:39:37 GMT

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;

Re: Load script - fields calc for missing fields

ogautier62 — Tue, 05 Jun 2018 13:47:48 GMT

yes, it should work :

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

Load A,

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

Resident Base table

group by A;

Re: Load script - fields calc for missing fields

seWork — Wed, 06 Jun 2018 06:47:17 GMT

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;

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);

drop table AvgDataTemp$(i);

Re: Load script - fields calc for missing fields

ogautier62 — Wed, 06 Jun 2018 13:00:04 GMT

ok,

try :

For i = 2 to vTable;

Load A,

Avg(FieldName(i,Base table))

Re: Load script - fields calc for missing fields

ogautier62 — Thu, 07 Jun 2018 09:48:26 GMT

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;

Re: Load script - fields calc for missing fields

seWork — Thu, 07 Jun 2018 10:11:33 GMT

Thank you,

I run it successfully!