Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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`;
HI
Try like this
Load * where NUM_MOIS&'_'&NUM_JOUR <> '2_29';
Select *
from
`004 le calendrier`;
But I don't want to delete the records from the years where february goes to 29 days !
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
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?
Also your function has the parameter divisor outside of the parenthesis, and it goes inside.
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.
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`;
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
Thanks for the answers, I've chose the Clever's one !