topic Set Analysis in QlikView

Set Analysis

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

NAME	STARTDT	ENDDT	STATUS	CYCLE_ID	Status
1	11/29/2016 10:54	11/29/2016 12:36	C	203	1
2	11/29/2016 9:30	11/29/2016 12:29	C	202	1
3	11/29/2016 9:00	11/29/2016 9:30	C	201	1

SuccessDate is a different field which will update my latest extract qvd refresh date.

If StartDt greater than SuccessDt & if C Count is 3 ---Success

If StartDt greater than SuccessDt & if C Count is not equal to 3 ---Failure

If StartDt is equal to SuccessDt & if C Count is 3 ---Do Nothing

If StartDt is equal to SuccessDt & if C Count is not 3 ---Failure

Note: Some times the Status is also P and End Date will be 11/29/9999

I've tried the following

=Count({< STATUS={'C'},SuccessDate = {">=Floor(STARTDT) "}>}SuccessDate)

=Count({< STATUS={'C'},STARTDT = {">=Floor(SuccessDate) = today()"}>}STARTDT)

stalwar1‌

Thanks.

Re: Set Analysis

Gysbert_Wassenaar — Thu, 01 Dec 2016 16:59:04 GMT

Perhaps something like this:

=Count({< STATUS={'C'},STARTDT = {">=$(=Floor(SuccessDate)) "}>}STARTDT )

If that doesn't work then post a small qlikview document with example data that demonstrates the problem.

Re: Set Analysis

stig1984 — Thu, 01 Dec 2016 17:01:33 GMT

Try

=If(Count(

STATUS = {'C'},

floor(STARTDT) = {">floor(SuccessDT)"}

STATUS) = 3,

"Success",

If(Count(

STATUS = {'C'}

STATUS) <>3,

"Fail",

Null()))

Re: Set Analysis

MK9885 — Thu, 01 Dec 2016 18:26:32 GMT

PFA.

And I tried your logic but it doesn't seem to work. You can adjust accordingly as I added Inline load but in realtime i'll be fetching it from db.

If you planning to reload the script the successdate might not work maybe if you can add that too in another Inline.

Thanks.

Re: Set Analysis

MK9885 — Thu, 01 Dec 2016 18:26:59 GMT

Thank but your expression has some syntax error.

I edited it to below

=If(Count({<STATUS = {'C'}, STARTDT = {">Floor(SuccessDate)"}>}

STATUS) = 3,'Success',

If(Count({<STATUS = {'C'}>}STATUS) <>3,'Fail',Null()))

Correct me if I did it wrong.

I've attached the sample file above....

Re: Set Analysis

MK9885 — Thu, 01 Dec 2016 19:09:44 GMT

=If(Count({<STATUS = {'C'}, STARTDT = {">=$(=Floor(SuccessDate))"}>}

STATUS) = 3,'Success',

If(Count({<STATUS = {'C'}>}STATUS) <>3,'Fail',Null()))

The above expression seem to be working.... Not fully tested but it is.. for now.

Re: Set Analysis

stig1984 — Fri, 02 Dec 2016 09:37:29 GMT

glad it worked - apologies for the syntax errors - wasn't 100% sure which dates you wanted as integers.