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

          hI,

          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,

          Stefano