
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Where and While?
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!
- Tags:
- new_to_qlikview

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
;
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try the below in Where clause of Query itself
SQL SELECT*
FROM APPS.ORACLE_TABLE_VIEW
WHERE Project_End_Date>= trunc(sysdate)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks, Gysbert! I can't find the mark as correct answer button. But this is terrific.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Awesome, thanks so much!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe with
WHILE addmonths(monthstart([Schedule Start Date] ),IterNo()-1) <= rangemin(makedate(2020,12,31),"SCHEDULE_END_DATE");
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'll give it a try and let you know how it works. Thanks again!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Finally got to try this - worked like a charm! Thanks again, Gysbert!
