Where and While?

[#1]

Hi, Community-

Is it possible to use a Where clause:

[Task No]

[GL_Code]

[Schedule Start Date]

[Schedule End Date]

.

.

Element_Name

Where Project_End_Date>= TODAY()  //only select schedule lines with end dates that haven't passed

SQL SELECT*

FROM APPS.ORACLE_TABLE_VIEW

in the same qlikview load as an interval/while loop? 

[Task No]

[GL_Code]

[Schedule Start Date]

[Schedule End Date]

.

.

Element_Name

[Schedule Start Date] + IterNo ()  as ReferenceDate

WHILE addmonths(monthstart([Schedule Start Date] ),IterNo()-1) <= "SCHEDULE_END_DATE";

SQL SELECT*

FROM APPS.ORACLE_TABLE_VIEW

Either works fine on its own, but when I try to do both things, only one of the prefixes will show as active (blue).

Part of this is I can't for the life of me figure out how to extract data using a date limited filter in the Oracle SQL, despite some excellent posts on the community and on oracle date prefixes.

Thanks!

[Gysbert_Wassenaar]

No, you can use either While or Where in a load statement. But there's no reason why you can't use two preceding loads:

MyTable:

[Task No]

[GL_Code]

[Schedule Start Date]

[Schedule End Date]

.

.

Element_Name

[Schedule Start Date] + IterNo ()  as ReferenceDate

WHILE addmonths(monthstart([Schedule Start Date] ),IterNo()-1) <= "SCHEDULE_END_DATE";

LOAD  * Where Project_End_Date>= TODAY();

SQL SELECT*

FROM APPS.ORACLE_TABLE_VIEW

Or try something like this:

LET vToday = num(today());

LOAD ...stuff... WHILE .... ;

SQL SELECT *

FROM APPS.ORACLE_TABLE_VIEW

WHERE "Project_End_Date" >= $(vToday)

;

[Kushal_Chawda]

Try the below in Where clause of Query itself

SQL SELECT*

FROM APPS.ORACLE_TABLE_VIEW

WHERE Project_End_Date>= trunc(sysdate)

[#4]

thanks, Gysbert! I can't find the mark as correct answer button. But this is terrific.

[#5]

Awesome, thanks so much!

[#6]

Hi, again Gysbert-

I just realized something about the way our data is created/stored that makes my date loop above (which is now working, thank you!) very large.

Regarding

Schedule Start Date] + IterNo ()  as ReferenceDate

WHILE addmonths(monthstart([Schedule Start Date] ),IterNo()-1) <= "SCHEDULE_END_DATE";

I totally forgot that end dates default to the year 4712 (no I'm not kidding) if the user doesn't end date a schedule line. I have no control over that process, so I need to modify the above "while" statement  so that it only iterates through, say 12/31/2020?

Thanks again.

[Gysbert_Wassenaar]

Maybe with

WHILE addmonths(monthstart([Schedule Start Date] ),IterNo()-1) <= rangemin(makedate(2020,12,31),"SCHEDULE_END_DATE");

[#8]

I'll give it a try and let you know how it works. Thanks again!

[#9]

Finally got to try this - worked like a charm! Thanks again, Gysbert!