Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
prem1234
Partner - Contributor II
Partner - Contributor II

set analysis expression for sql query

Can some help with equivalent set  analysis expression for below sql query.

 

 


SELECT COUNT (DISTINCT A.PARTY_INSURED_ID)

  FROM PD.BI_INS_STS A

WHERE     A.INSURANCE_TYPE_ID <> 5

       AND A.CONTRACT_STATUS = 1

       AND A.CONTRACT_ID IN (SELECT CONTRACT_ID

                               FROM BI_INS_SALARY_CHANGE

                              WHERE STATUS = 1)

       AND ((       TO_CHAR (A.CONTRACT_START_DATE, 'YYYYMM') <= 202111

                AND (TO_CHAR (NVL (A.RESIGNATION_DATE, SYSDATE) + 1,

                              'YYYYMM') >

                     202111)

             OR (    TO_CHAR (A.CONTRACT_START_DATE, 'YYYYMM') = 202111

                 AND (  TRUNC (A.CONTRACT_START_DATE, 'MONTH')

                      - TRUNC (A.CONTRACT_END_DATE, 'MONTH')) =

                     0)));


I have tried to generate the master calendar for start date and end date then final set expression is like this but  output is not matching with query

count({< ContractStartDateKey={"<= $(vFS_CY_Start)"}, InsuranceTypeID-={5}, CONTRACT_STATUS={1}, SAL_STATUS={1},
ResignationDateKey={"> $(vFS_CY_End)"} , MonthYear =,Year = , Month =>//} distinct InsuranceNumber)
+
<ContractStartDateKey={">= $(vFS_CY_Start) <= $(vFS_CY_End)"}, InsuranceTypeID-={5}, CONTRACT_STATUS={1}, SAL_STATUS={1}, DUMMY_CONTRACT_STATUS={1},MonthYear =,Year = , Month =
>} distinct InsuranceNumber)  

in the above   vFS_CY_Start = min(DateKey)

                        vFS_CY_End = max(DateKey) 

                         if(date(Monthstart(CONTRACT_END_DATE))-date(Monthstart(CONTRACT_START_DATE))=0,1,0) as DUMMY_CONTRACT_STATUS,

 

 

 

Labels (2)
0 Replies