Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fors_gregg
New Contributor II

Chart expression equivalent to sql "exists"?

Hi,

I am trying to count the number of cases, in any current status, but only if they have ever been in a specific min(status).

Thanks in advance for any suggestions.

Fors

6 Replies

Re: Chart expression equivalent to sql "exists"?

Would you be able to share few rows of data with the expected output from them?

aunahsan
New Contributor III

Re: Chart expression equivalent to sql "exists"?

If i have understood this correctly;

i think , you would just be able to do a simple set analysis, without any date modifiers and specifying what the specific status is as a modifier. a DISTINCT of the count should give you the number of unique cases, that has ever had a the specific status.

Count({<Status={'$(specific_status)'}>}DISTINCT CASES)

fors_gregg
New Contributor II

Re: Chart expression equivalent to sql "exists"?

Thanks, Aun!

Using your example, I did this:

Count({<min(NEW_CMPLN_STATUS_CDE)={'$('30')'}  DISTINCT CMPLN_ID)

but it's returning the "-"

fors_gregg
New Contributor II

Re: Chart expression equivalent to sql "exists"?

So Basicaly...

Case#, Curr_Status, Status_History

                                    (includes)

1           90                         10,15,20,30,36,90

2           90                          10,20,30,42,90

3           90                          10,15,20,90

4           30                          10,30

90 = Closed

Attempting to return all in Status 90, but only if they have ever been in status 30.

I would expect to return only Case # 1, and 2 (since 3 was never in status 30, and obviously 4 is not in 90.)

Re: Chart expression equivalent to sql "exists"?

May be like this

Sum({<Curr_Status = {'90'}, Status_History = {"*30*"}>}Measure)

or this if Status_History is not really a column

Sum({<Curr_Status = {'90'}, Case# = p({<Status = {'30'}>})>} Measure)

sasiparupudi1
Honored Contributor III

Re: Chart expression equivalent to sql "exists"?

Try

Sum(If(SubStringCount(Status_History,'90') and SubStringCount(Status_History,'30'),1,0))

Community Browser