topic Re: Where and While? in QlikView

Where and While?

Wed, 05 Aug 2015 20:32:28 GMT

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!

Re: Where and While?

Gysbert_Wassenaar — Thu, 06 Aug 2015 06:11:36 GMT

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)

;

Re: Where and While?

Kushal_Chawda — Thu, 06 Aug 2015 06:24:23 GMT

Try the below in Where clause of Query itself

SQL SELECT*

FROM APPS.ORACLE_TABLE_VIEW

WHERE Project_End_Date>= trunc(sysdate)

Re: Where and While?

Thu, 06 Aug 2015 13:58:37 GMT

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

Re: Where and While?

Thu, 06 Aug 2015 13:58:47 GMT

Awesome, thanks so much!

Re: Where and While?

Thu, 06 Aug 2015 16:08:53 GMT

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.

Re: Where and While?

Gysbert_Wassenaar — Thu, 06 Aug 2015 16:14:31 GMT

Maybe with

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

Re: Where and While?

Thu, 06 Aug 2015 17:02:28 GMT

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

Re: Where and While?

Fri, 14 Aug 2015 18:04:44 GMT

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