topic Re: FieldValue and FieldValueCount not working with Preceding load in QlikView

FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: FieldValue and FieldValueCount not working with Preceding load

ogautier62 — Tue, 24 Jul 2018 12:13:13 GMT

Hi,

what result do you want ?

min and max for all, for each ID ?

regards

Re: FieldValue and FieldValueCount not working with Preceding load

sunny_talwar — Tue, 24 Jul 2018 12:14:23 GMT

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....

Re: FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Tue, 24 Jul 2018 12:22:36 GMT

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

Re: FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Tue, 24 Jul 2018 12:23:35 GMT

Hi Sunny,

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

Thanks,

Ravi

Re: FieldValue and FieldValueCount not working with Preceding load

ogautier62 — Tue, 24 Jul 2018 12:28:15 GMT

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

Re: FieldValue and FieldValueCount not working with Preceding load

sunny_talwar — Tue, 24 Jul 2018 12:29:51 GMT

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;

Re: FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Tue, 24 Jul 2018 12:30:29 GMT

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

Re: FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Tue, 24 Jul 2018 12:40:47 GMT

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

Regards,

Ravi

Re: FieldValue and FieldValueCount not working with Preceding load

sunny_talwar — Tue, 24 Jul 2018 12:42:50 GMT

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;

Re: FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Tue, 24 Jul 2018 12:52:57 GMT

Yes Sunny. The code which you have sent is working. but what i am really wondering is the script which i have mentioned in my original post worked for me before and not working now.

Re: FieldValue and FieldValueCount not working with Preceding load

sunny_talwar — Tue, 24 Jul 2018 12:55:43 GMT

Well rwunderlich‌ might have an idea of how did this ever work

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

];

Unless you have another reference of Sales field before the AutoGenerate table..... If you never had a reference of Sales before AutoGenerate.... I believe that this should have never worked....

Re: FieldValue and FieldValueCount not working with Preceding load

ravindraqv9 — Tue, 24 Jul 2018 13:12:25 GMT

Hi Sunny,

I might be wrong. Thanks for all your inputs.

Regards,

Ravi

Re: FieldValue and FieldValueCount not working with Preceding load

sunny_talwar — Tue, 24 Jul 2018 13:13:09 GMT

No problem my friend... but you might be wrong about what? That it worked in the past?

Re: FieldValue and FieldValueCount not working with Preceding load

rwunderlich — Tue, 24 Jul 2018 16:37:36 GMT

I can't imagine this "working" -- as written. Perhaps some variation did, or an earlier QV version did not throw an error but did not return a correct result either.

-Rob