FieldValue and FieldValueCount not working with Preceding load

[ravindraqv9]

Hi All,

Hope everyone is doing good.

I have an issue with FieldValue()  and FieldValueCount() functions when used in Preceding load as given below.

Load Min(FieldValue('Sales',RecNo())) as MinSales,

         Max(FieldValue('Sales',RecNo())) as MaxSales

AutoGenarate FieldValueCount('Sales')

Load * Inline

[

ID,Sales

1,100

2,200

3,300

4,400

];

When i used the above script i am getting null value in both fields.

Kindly let me know if anyone having idea why it is giving null values.

Thanks in advance,

Ravi

[ogautier62]

Hi,

what result do you want ?

min and max for all, for each ID ?

regards

[sunny_talwar]

This actually isn't a preceding load... you are using AutoGenerate for the FieldValue table making it a separate load table for the Inline Table load....

[ravindraqv9]

Hi,

I need to get Min and Max Sales values not for each ID.The resultant table should have only one record like below

MinSales MaxSales

100           400

Thanks,

Ravi

[ravindraqv9]

Hi Sunny,

Thanks for the response. What is wrong in my script as i am getting an error (Autogenarate out of range).

Thanks,

Ravi

[ogautier62]

so

load min(Sales) as minSales, max(Sales) as maxSales resident 'your table Sales';

[sunny_talwar]

I don't think you can add AutoGenerate table before the Inline Table... because at that time Sales isn't even a field... on the same note... I don't think FieldValue on Sales will work within the preceding load... I am not 100% confident about this... but it seems that FieldValue can only work on a field which has already been created before this table is loaded...

So, having said that... you have this option

Table:

LOAD * INLINE [

    ID, Sales

    1, 100

    2, 200

    3, 300

    4, 400

];

AggrTable:

LOAD Min(Num(FieldValue('Sales', RecNo()))) as MinSales,

     Max(Num(FieldValue('Sales', RecNo()))) as MaxSales

AutoGenerate FieldValueCount('Sales');

DROP Table Table;

[ravindraqv9]

Hi,

That works. but i want to use FieldValue(). The script which i have used worked before but not working now. So want to know why it is not working.

Thanks,

Ravi

[ravindraqv9]

The code worked for me before but now it is no longer working. Thanks for the inputs Sunny.

Regards,

Ravi

[sunny_talwar]

Did you add the Num() function?

Table:

LOAD * INLINE [

    ID, Sales

    1, 100

    2, 200

    3, 300

    4, 400

];

AggrTable:

LOAD Min(Num(FieldValue('Sales', RecNo()))) as MinSales,

    Max(Num(FieldValue('Sales', RecNo()))) as MaxSales

AutoGenerate FieldValueCount('Sales');

DROP Table Table;