Problem with Mod()

[#1]

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.

[MayilVahanan]

HI

Try like this

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

Select *

from

`004 le calendrier`;

[#3]

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

[Anonymous]

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

[christian77]

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?

[christian77]

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

[swuehl]

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)

[Clever_Anjos · Accepted solution]

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`;

[christian77]

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

[#10]

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