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

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis Questions - need help

Hello,

Could someone kindly translate this QV statement to SQL statement for me?

SUM(AGGR(count(DISTINCT

{<

        [ProcedureType] = {'*'} - {'ADJ'}

>}

[ProcedureID]),  [ProcedureType],%KEY1, %KEY2, %KEY3))

Thanks

9 Replies
sunny_talwar

May be this

SELECT Sum(PROC_ID)

FROM(

SELECT

     %KEY1,

     %KEY2,

     %KEY3,

     Count(DISTINCT ProcedureID) as PROC_ID

FROM ....

WHERE ProcedureType not in ('ADJ')

GROUP BY

     %KEY1,

     %KEY2,

     %KEY3);

Anonymous
Not applicable
Author

Hi,

Thanks for the quick reply.

I ran your sql statement against my db.  The total I am getting is much much bigger than the value

in the QV app.

Is there anything I'm missing?

Thanks

Anonymous
Not applicable
Author

In the QV app, there's a variable which points to that QV command listed in my original post.  This

number is much much smaller than from the sql command.

Anonymous
Not applicable
Author

I am sorry.  It was a mistake on my part.  The number matches. 

Thank you!!

Anonymous
Not applicable
Author

Now that I know

[ProcedureType] = {'*'} - {'ADJ'} = ProcedureType not in ('ADJ')

Can you explain in English this part of the QV --> {'*'} - {'ADJ'}?

pradosh_thakur
Master II
Master II

SELECT Sum(A.Sum_procedure)

FROM(

SELECT   ProcedureType

     %KEY1,

     %KEY2,

     %KEY3,

     Count(DISTINCT ProcedureID) as Sum_procedure

FROM ....

WHERE ProcedureType not in ('ADJ')

GROUP BY

ProcedureType

     %KEY1,

     %KEY2,

     %KEY3) A;


regards

Pradosh

Learning never stops.
sunny_talwar

Include all rows where ProcedureType has a value, but one of those values is not ADJ... So, include every row for ProcedureType except Null and ADJ

Anonymous
Not applicable
Author

Hi Sunny,

Yes, I understand that's the outcome.  I guess when I am reading the QV code.  I am reading

it as '*' minus 'ADJ'.  That's what's confusing to me.  I guess I need to read up on the syntax documentation

in details.

sunny_talwar

I am not sure what you are confused about.... Think of it like this

'*' -> Everything which is not null

'Adj' -> Where the value is Adj

'*' - 'ADJ' -> Exclude 'Adj' from everything which is not null...

RowNo,     ProecdureType

1,     abc

2,     def

3,    

4,     ADJ

5,     AKJH

Assuming RowNo = 3 has ProcedureType Null, '*' -'ADJ' will include 1, 2, and 5

Does this make sense?