1 Reply Latest reply: Sep 20, 2011 10:13 AM by stefano santini RSS

    Writing SubQuery logic in Set Analysis

      I have a scenario as below,   


      There is a fact A_FACT and is having fields like New_Joinee_Flag, Offer_Flag, Candidate_ID, Month_SID etc. the requirement is to find New_Joinee_Count for selected month and next month where the Candidate is having a offer in selected month.




      below is the respective query used,



      select sum(NEW_JOINEE_FLAG) from hcm_A_FACT where month_sid in( 201104,201105) and  CANDIDATE_SID in (


      select CANDIDATE_SID from A_FACT where offer_flag =1 and month_sid = 201104)




      since this needs to be dynamic can't write it in script level.




      Anyone please help me in writing the same as set analysis expression.

        • Writing SubQuery logic in Set Analysis
          stefano santini


          i think that your query must be something like this when u select month SID 201105


          sum({$<CANDIDATE_SID =P({$<month_sid ={$(#=only(month_sid )-1)},offer_flag={1} >}CANDIDATE_SID )>}NEW_JOINEE_FLAG)


          This is an example, if u attach a file with some example data is better.

          But i hope this help u


          C u,