MTD filter with apply comparision

[mstms]

can any one tell me how this apply comparision works in filtering the MTD values of cycle id which is the date, when a particular date value is given in the prompt.

filter mtd

applycomparision(“#0<=#1”,cycle(ID),{cycle date prompt})
and
applycomparision(“#0>=(select distinct trunc(Z_cycle_id,MM) from PS_Z_TDAY where Z_CYCLE_ID=#1)”,cycle(id),{Cycle date prompt})

 

[Z3]

applicomparison is like an where clause. #0 is the first arguement, and so on. So what's your question about ?

 

[mstms]

Hi Z3,
thanks for your reply, i am just wondering what the second apply comparision does here when it combines with the first one,
thanks

 

[Z3]

It's a WHERE clause SQL. If you replace #0 with cycle@ID, #1 with the date prompt, you get the exact WHERE clause.

 

[FLB]

That query is going to be a DB killer..... You might want to look into migrating to a "real" transformation table....

In your case, the SQL will do something like this:

select ...
from ....
where ....
and
Cycle_ID <= to_date("01/15/2005")
and
Cycle_ID >= (select distinct trunc(Z_Cycle_ID,MM) from PS_Z_TDAY where Z_CYCLE_ID=to_date("01/15/2005")

So you are doing a "only retain all the cycle_ids from the beginning of the month until the cycle I selected".

You can do exactly the same thing without any applycomparison and just "clean" relationship filters.

2 cents,
FLB

 

[mstms]

Hi FLB,
Thanks for your reply,This is really helpful to me,

mstms