Is the problem the comma?

[#1]

Hi to everyone,

I'm new in qlikview but i'm going mad with this problem.

I need to parse the entire result set of a load to find the row with minimun value of a ratio of two field.

This is the code that i did

let ratioMin=1;

let ratioMax=0;

For i = 1 to NoOfRows('Table')

  LET vNumA=FieldValue('NumberA',$(i));

  LET vNumB= FieldValue('NumberB',$(i));

  LET vNewRatio=$(#vNumA)/$(#vNumB);

  if( $(ratioMin)>>$(vNewRatio)) then

   let ratioMin=$(vNewRatio);

   let vUteDanMin=FieldValue('Utente',$(i));

  end if

next

The script return error, and with the debug i saw that the variable vNewRatio assume value like 0,066666666666667 with comma separator.

The returned message is this:

"Script line error:

if( 1>>0,066666666666667) then"

Is that the problem? How can i fix it?

thanks a lot

[#2]

Ok,

i tryed and works fine. Hower your script works fine also without Num() function,don't you?

vRatio assume the value 0.66666666666667 (with dot as separator instead comma) as i can see in debug mode.

Could be the problem by the FieldValue() ?

Thanks a lot ....you are great!!

Alessio

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Yes, the FieldValue() would be the case, but that's easy to fix, just modify the lines

LET vNumA = Num(FieldValue('NumberA',$(i)));

And if that does not work:

LET vNumA = Num(Num#(FieldValue('NumberA',$(i)), '#,#'));

You can even specify the decimal and thousand separator in the Num() function without need to represent with the Num#() function

Hope that helps.

Miguel

[rwunderlich]

That seems like a complex approach, looping through the records. There are a number of simpler approaches using LOAD. One I can think of is:

MinTable:

LOAD

          FirstSortedValue(DISTINCT Utente, (NumberA / NumberB)) as MinUtente

RESIDENT Table

;

-Rob

http://robwunderlich.com

[#5]

Hi Miguel,

the problem was the FieldValue(), maybe the result of the function was a strange format...I inserted the Num() function and the problem is gone...However i had other problem with FieldValue(), so i changed it with Peek().

FiledValue have strange behaviour...

Miguel thanks a lot....

Rob, you have right, your solution maybe was easier, but to retrieve the entire row i need to make e left join with itself i think...However i wanted just to understand the strange problem that i found! Thanks a lot also to you

Alessio

[rwunderlich]

I understand your data exists in two tables, not the same table.

Note that the fieldvalue() technique works only if every row has a unique value. FieldValue() is the set of values, not rows. So row values like

1

3

3

4

Will return only three values 1,3,4.

If you want to go actual row by row, use peek() instead of fieldvalue().

-Rob