topic Re: Problem with Fieldvalue() function in QlikView

Problem with Fieldvalue() function

Anonymous — Tue, 21 Jun 2016 13:51:31 GMT

Hello

I want to extract the minimum date of one table (the dates are decimal fields in the DB) and use it fo make a for loop.

What I have done was:

TempMin:

LOAD *;

SQL

SELECT min($(Date_Field)) as minDate

FROM $(Table_Name)

WHERE $(Company_Name) = $(company);

let varMinMonth = FieldValue('minDate',1);

And I have always a null value in the varMinMonth variable, but when I put the field minDate in a TableBox it shows the correct value.

If I ceate one inline table, the result is OK

LOAD * INLINE [

English, German

Hello, Hallo

This is a test, Dies ist ein Test

];

let a =FieldValue('English',1);

Anyone can help me on this?

Thnak you

Re: Problem with Fieldvalue() function

swuehl — Tue, 21 Jun 2016 19:08:52 GMT

Not exactely sure what causes your issue.

Could you maybe create a small sample QVW and post it to this thread?

(just your short script that loads the data and the variable, with the script executed, so we can look at your table and values)

I usually use the Peek() function to address table values:

let varMinMonth = Peek('minDate',0, 'TempMin');

Re: Problem with Fieldvalue() function

Anonymous — Wed, 22 Jun 2016 14:21:43 GMT

Hello Swuehl

In the thread I dont have the option to attach files others than pictures or videos. I have creted a small QVD and loadded it using the script bellow.

TempMin:

LOAD max(OKRGDT)

FROM $(qvdFile)(qvd);

let varMinMonth = FieldValue('minDate',1);

Test:

LOAD * INLINE [

English, German

Hello, Hallo

This is a test, Dies ist ein Test

];

let a =FieldValue('English',1);

As you can see bellow the variable is varMinMonth = null.

Bellow an example of the data in the QVD file. It is a decimal field that represents a date.

20100927

20101227

20101228

20101229

20101230

20110107

20110110

20110111

20110112

20110117

20110119

20110120

20110223

20110228

Re: Problem with Fieldvalue() function

swuehl — Wed, 22 Jun 2016 16:03:57 GMT

You can attach files in the 'advanced editor' in the forum, please follow

Uploading a Sample

Please post the sample QVD.

Re: Problem with Fieldvalue() function

trdandamudi — Wed, 22 Jun 2016 16:12:33 GMT

FieldValue function will only work with distinct field values. So as Stefan suggested please use:

let varMinMonth = Peek('minDate',0, 'TempMin');

Hope this helps...

Re: Problem with Fieldvalue() function

oscar_ortiz — Wed, 22 Jun 2016 20:43:41 GMT

Where are you defining the "minDate" field. The FieldValue() function will fail silently if your field does not exist.

Did you possibly mean to do this?

TempMin:

LOAD max(OKRGDT) as minDate

FROM $(qvdFile)(qvd);

let varMinMonth = FieldValue('minDate',1);

Re: Problem with Fieldvalue() function

Anonymous — Thu, 23 Jun 2016 09:37:23 GMT

Hello Guys

Thank you for your help, I have been able to discover that If I load the data from a QVD file:

TempMin:

LOAD OKRGDT as minDate

FROM $(qvdFile)(qvd);

let varMinMonth = FieldValue('minDate',1);

let varMinMonth2 = Peek('minDate',0, 'TempMin');

Everything works fine.

But if I load the data directly from the database:

TempMin:

LOAD *;

SQL

SELECT min(OKRGDT) as minDate

FROM OCUSMA

WHERE OKCONO = 200;

let varMinMonth = FieldValue('minDate',1);

let varMinMonth2 = Peek('minDate',0, 'TempMin');

let varMinMonth1 = FieldValue('MINDATE',1);

I works only when I use MINDATE in uppercase. I can work with it, but any idea why?

Thank you for your help

Felipe

Re: Problem with Fieldvalue() function

swuehl — Thu, 23 Jun 2016 13:09:02 GMT

Felipe, which field name spelling is actually shown in the table view?

This should be the correct spelling when addressing the field using QlikView functions.

Re: Problem with Fieldvalue() function

marcus_sommer — Thu, 23 Jun 2016 13:18:27 GMT

One reason for this behaviour could be that fieldvalue() only grabbed the numeric value and not provide a string-interpretation like in this case: Re: Problem with FieldValue. Therefore try something like:

let varMinMonth = date(num(FieldValue('minDate',1)));

or maybe

let varMinMonth = dual(date(FieldValue('minDate',1)), num(FieldValue('minDate',1)));

- Marcus

Re: Problem with Fieldvalue() function

Anonymous — Thu, 23 Jun 2016 14:01:33 GMT

In teh table view the name of the filed is MINDATE instead of minDate

Re: Problem with Fieldvalue() function

Anonymous — Thu, 23 Jun 2016 14:13:06 GMT

I have tried but does not solve the problem.

Felipe

Re: Problem with Fieldvalue() function

swuehl — Thu, 23 Jun 2016 15:21:29 GMT

So your SQL driver returns all field names in upper case.

You can rename the field name to mixed case in the preceding LOAD statement or just use the upper case field name in your FieldValue() function, whatever you like better.

Re: Problem with Fieldvalue() function

mblumenroth — Sat, 22 Sep 2018 21:14:05 GMT

Hi Felipe,

ran into the same issue.

FieldValue only seems to work on columns that contain unique values.

My solution was to use the peek function, as originally suggested by Stefan.