Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problem with Mod()

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.

1 Solution

Accepted Solutions
Employee
Employee

Re: Problem with Mod()

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

9 Replies

Re: Problem with Mod()

HI

Try like this

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

Select *

from

`004 le calendrier`;

Not applicable

Re: Problem with Mod()

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

Re: Re: Problem with Mod()

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
Valued Contributor

Re: Problem with Mod()

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
Valued Contributor

Re: Problem with Mod()

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

MVP
MVP

Re: Problem with Mod()

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)

Employee
Employee

Re: Problem with Mod()

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
Valued Contributor

Re: Problem with Mod()

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

Not applicable

Re: Problem with Mod()

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

Community Browser