Qlikview Set Analysis

[sanketkhunte]

Hi there,

How can i convert below expression into Set Analysis ?

COUNT
(
DISTINCT
IF(
(
SC_ENABLED_STATE = 1 or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE = 1) OR
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') <= DATE(SC_START_DATE,'MM/DD/YYYY') ) OR
(DATE( SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY')) or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE <> 1 AND DATE(SC_MODIFY_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY') )
)

,ID))

Many Thanks

Sanket

[sanketkhunte]

Please see below expression with IF-ELSE which is working. Need to convert this into SET ANALYSIS syntax.

COUNT
(
DISTINCT
IF(

(DATE(PC_FIRST_ACTIVATED_DATE,'MM/DD/YYYY') <= DATE((v_Max_SC_Start_Date),'MM/DD/YYYY')) AND
(
SC_ENABLED_STATE = 1 or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE = 1) OR
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') <= DATE(SC_START_DATE,'MM/DD/YYYY') ) OR
(DATE( SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY')) or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE <> 1 ANDDATE(SC_MODIFY_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY') )
)

,ID))

[PrashantSangle]

Hi,

try including And condition expression in every set of Or condition

try below,

Count( DISTINCT {
  <SC_ENABLED_STATE={1},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
  <SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
  <SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
  <SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
  <SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}
PARTNER_ACCOUNT_ID)

Regards

[sanketkhunte]

It is fetching 0 value. Where as IF -- ELSE Statement fetching 291 value which is correct.

Can we have other option to add AND operator.

Many Thanks

Sanket

[PrashantSangle]

Hi,

might be other experts can give better solutions for this.

Dear All,

swuehl‌, jagan‌, tresesco‌, sunindia‌

Please suggest.

Regards,

[sunny_talwar]

May be the variable is different:

Count( DISTINCT {
<SC_ENABLED_STATE={1}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''}, SC_ENABLED_STATE-={1}, SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}>} PARTNER_ACCOUNT_ID)

[sanketkhunte]

Thanks Sunny.

Variable is v_EndDate not v_Max_SC_Start_Date.

Many Thanks

Sanket

[sunny_talwar]

But the if statement you showed above was comparing it again v_Max_SC_Start_Date

[sanketkhunte]

Thanks for pointing out this but we need to use v_EndDate

Sorry for the inconvenience cause !!

Many Thanks

Sanket

[sunny_talwar]

Is the issue resolved then?

[sanketkhunte]

How can we add PC_ACTIVATED_DATE as AND clause in below set expression

Count( DISTINCT {
  <SC_ENABLED_STATE={1}> +
  <SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
  <SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
  <SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
  <SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}