topic Re: Is the problem the comma? in QlikView

Is the problem the comma?

Tue, 24 Jul 2012 12:34:58 GMT

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

Re: Is the problem the comma?

Miguel_Angel_Baeyens — Tue, 24 Jul 2012 12:37:25 GMT

Hi,

Use just ">" instead of ">>" (the latter is a unary operator, unlikely to be used here). That should work just fine.

Hope that helps.

Miguel

Re: Is the problem the comma?

Tue, 24 Jul 2012 12:45:45 GMT

Hi Miguel,

I tried your solution but doesn't work. There is always the error, now with one ">".

However thank you for the information

Alessio

Re: Is the problem the comma?

Miguel_Angel_Baeyens — Tue, 24 Jul 2012 12:55:00 GMT

Hi Alessio,

Try the following:

IF Num($(ratioMin)) > Num($(vNewRatio)) THEN

Hope that helps.

Miguel

Re: Is the problem the comma?

Tue, 24 Jul 2012 13:16:23 GMT

Hi again,

i tryed also this but dosn't work

the variable NUM(vNewRatio) assume value lik this 066666666666667 instead 0,66666666666667, and the IF don't work properly.

I think the problem is the ',' that the ratio give back to the variable instead the '.' as decimal separator as usual, but i don't understand why

Thanks a lot

Alessio

Re: Is the problem the comma?

Miguel_Angel_Baeyens — Tue, 24 Jul 2012 13:50:34 GMT

Hi Alessio,

What do your script variables ThousandSep, DecimalSep and so store? Are they properly set up and according to your computer local settings?

Miguel

EDIT: Attached an example. If you remove the Num() function in the If, it crashes, otherwise it works as expected.

Re: Is the problem the comma?

Tue, 24 Jul 2012 14:05:00 GMT

These are my eviroment variable:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

I tryed also to switch ThousandSep and DecimalSep .

SET ThousandSep=',';

SET DecimalSep='.';

but the problem is still there.

My local setting of Decimal Separator is ','. Do you think is that?

Thanks a lot

Re: Is the problem the comma?

Miguel_Angel_Baeyens — Tue, 24 Jul 2012 14:18:07 GMT

Alessio,

Does my app run fine when you load it?

Miguel

Re: Is the problem the comma?

Tue, 24 Jul 2012 14:20:47 GMT

Sorry i have the personal edition for the moment, can you paste the just the code?

I tried to change the windows enviroment variable but nothing is changed

Alessio

Re: Is the problem the comma?

Miguel_Angel_Baeyens — Tue, 24 Jul 2012 14:23:38 GMT

Alessio here you are,

// Same variables as you have
LET vNum1 = 2;
LET vNum2 = 3;
LET vRatio = $(vNum1) / $(vNum2); // To make sure the result is a long decimal number 0,6666666667
IF Num($(vRatio)) > 1 THEN // This works, enters the IF part
     SET vRatioOK = 0;
ELSE 
     SET vRatioOK = 1;
END IF

You will see the variable vRatioOK = 0, that means that has run as expected

Miguel

Re: Is the problem the comma?

Tue, 24 Jul 2012 14:33:41 GMT

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

Re: Is the problem the comma?

Miguel_Angel_Baeyens — Tue, 24 Jul 2012 14:45:24 GMT

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

Re: Is the problem the comma?

rwunderlich — Tue, 24 Jul 2012 17:39:48 GMT

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

Re: Is the problem the comma?

Wed, 25 Jul 2012 10:31:03 GMT

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

Re: Is the problem the comma?

rwunderlich — Wed, 25 Jul 2012 15:14:59 GMT

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

Will return only three values 1,3,4.

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

-Rob