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

[seWork]

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

[ogautier62]

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

[seWork]

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

[ogautier62 · Accepted solution]

Hi,

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

[seWork]

Thank you

it's working now