topic Problem with Mod() in QlikView

Problem with Mod()

Thu, 17 Oct 2013 13:51:10 GMT

Hi, here I come again for a Where statement problem.

I don't want to select the february 29th of every year, except the bissextile ones.

So I have in my script :

(num_annee is the year, so Mod(2012,4) = 0 , there is 29 days in february )

LOAD `COD_COLL`,`IDENT_ID` as [ID_ABSENT],`IDENT_ID`, `IDF_AGENT`, `IDF_CLE`, `NUM_ANNEE` , `NUM_MOIS`, `NUM_JOUR`, `TYP_ARRET`, `COD_ARRET`, `COD_PAIEM`, `COD_TPAIE`, `IND_CALEND`, `IND_TRENT`, `DAT_MAJ`, `USER_MAJ`;

table_calendrier:

SQL SELECT *

FROM `004 le calendrier`

WHERE NOT (((Mod(NUM_ANNEE),4)<>0) AND (NUM_MOIS=2) AND (NUM_JOUR=29));

But it gave me an error, and said that there is a missing operator. So I tried only : "Where ((Mod(2012,4))=0)", same error.

I don't see where the bug is. Can someone do sth for me ? Thanks a lot.

Re: Problem with Mod()

MayilVahanan — Thu, 17 Oct 2013 13:54:31 GMT

Try like this

Load * where NUM_MOIS&'_'&NUM_JOUR <> '2_29';

Select *

from

`004 le calendrier`;

Re: Problem with Mod()

Thu, 17 Oct 2013 14:03:49 GMT

But I don't want to delete the records from the years where february goes to 29 days !

Re: Re: Problem with Mod()

Anonymous — Thu, 17 Oct 2013 16:02:31 GMT

Antonin

Why not create a QlikView Master Calendar [google that you find out to do it], since Qlikview auto handles leap years having 29-Feb and the other years not and also sorts the exceptions to this.

Then inner join this on a date field to your table_calendrier table and you'll only be left with valid dates.

If I have misunderstood what you are trying to achieve then please clarify.

Best Regards, Bill

Re: Problem with Mod()

christian77 — Thu, 17 Oct 2013 16:26:17 GMT

Hi.

It's weird how mod does not work in an expression alone. I attached a file.

1900 did not have feb 29th. Time adjustments?

Re: Problem with Mod()

christian77 — Thu, 17 Oct 2013 16:27:56 GMT

Also your function has the parameter divisor outside of the parenthesis, and it goes inside.

Re: Problem with Mod()

swuehl — Thu, 17 Oct 2013 16:50:10 GMT

That's because 1900 wasn't a leap year:

Leap year - Wikipedia, the free encyclopedia

Antonin, you can't use QV functions in a SQL statement WHERE clause. You need to check if there is a SQL function available that does the same for your DBMS or use the mod() in a LOAD statement.

e.g.

Modulo (Transact-SQL)

Re: Problem with Mod()

Clever_Anjos — Thu, 17 Oct 2013 17:45:17 GMT

Antonin, please note that what is written after "SQL" reserved word is outside QlikView domain. It´s resolved by your database and ODBC Driver. QlikView functions only resides at a LOAD comand.

You can rewrite it this way

table_calendrier:

LOAD *
WHERE NOT (((Mod(NUM_ANNEE),4)<>0) AND (NUM_MOIS=2) AND (NUM_JOUR=29));
SQL SELECT *
FROM `004 le calendrier`;

Re: Problem with Mod()

christian77 — Fri, 18 Oct 2013 07:20:46 GMT

Yes. Leap year means exactly the same. A leap year (or intercalary or bissextile year)

here goes that algorithm.

if year is divisible by 400 then

is_leap_year

else if year is divisible by 100 then

not_leap_year

else if year is divisible by 4 then

is_leap_year

else

not_leap_year

Re: Problem with Mod()

Fri, 18 Oct 2013 13:27:44 GMT

Thanks for the answers, I've chose the Clever's one !