topic Re: How could i create dynamic table that will calc num field and peek random values from text field? in QlikView

How could i create dynamic table that will calc num field and peek random values from text field?

seWork — Tue, 12 Jun 2018 08:38:47 GMT

Hi all,

I created a dynamic table load that loading the fields that the user choose

Item_Master_Data:

LOAD

Distinct $(vItem_Master_Data_Calc)

FROM

Item_Master_Data.qvd

(qvd);

at the moment i success to create a calculated table

as describe below

let vTable = NoOfFields('Item_Master_Data');

avgitemtable :

load distinct Category resident Item_Master_Data;

for i = 2 to vTable

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

inner join (avgitemtable)

Load Category

,CEIL(Avg($(fname))) as AVG.$(fname)

Resident Item_Master_Data group by Category;

I would like to find a way to load the avg. calc only if the $(fname) is a num field

otherwise to peek random value from the $(fname) field in Item_Master_Data table

for example, if the Item_Master_Data will look like

SKU | Category(Text) | units per pallet(Num) | SupplierID(Text)

1 |A |10 | 4

2 |A |20 | 22

3 |B |15 | 4

4 |B |15 |15

5 |C |6 |15

6 |D |7 |22

and then the avgitemtable supposed to look like

Category(Text) | units per pallet(Num)-Avg Calc | SupplierID(Text) - Random peek

A |15 |22

B |15 |4

C |6 |15

D |7 |22

Thank you

Re: How could i create dynamic table that will calc num field and peek random values from text field?

ogautier62 — Tue, 12 Jun 2018 09:13:30 GMT

Hi,

to test if num :

load if(isnum(maxString(your field)),avg(conso), ' a random value') as .....

for random text value you can try this :

subfield(concat(your field,'/'),'/',ceil(rand()*count(your field))) from .... group by category

this pick a random value in concat string of all values

regards

Re: How could i create dynamic table that will calc num field and peek random values from text field?

seWork — Tue, 12 Jun 2018 10:45:49 GMT

Thank you,

It works for the AVG

but not for the random

The way that I tried to write it:

inner join (avgitemtable)

Load Category

,if(isnum(maxString($(fname))),CEIL(Avg($(fname)),subfield(concat($(fname),'/'),'/',ceil(rand()*count($(fname)))))) as AVG.$(fname)

Resident Item_Master_Data group by Category;

I'm getting nulls instead of random values

Re: How could i create dynamic table that will calc num field and peek random values from text field?

ogautier62 — Tue, 12 Jun 2018 12:21:53 GMT

Hi,

one parenthesis missing in your first CEIL maybe : must be CEIL(avg($(fname)) )

Re: How could i create dynamic table that will calc num field and peek random values from text field?

seWork — Tue, 12 Jun 2018 12:43:24 GMT

Thank you

it's working now