topic Re: Set Analysis Questions - need help in QlikView

Set Analysis Questions - need help

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

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

Re: Set Analysis Questions - need help

sunny_talwar — Mon, 16 Oct 2017 19:25:47 GMT

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);

Re: Set Analysis Questions - need help

Anonymous — Mon, 16 Oct 2017 19:33:06 GMT

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

Re: Set Analysis Questions - need help

Anonymous — Mon, 16 Oct 2017 19:34:12 GMT

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.

Re: Set Analysis Questions - need help

Anonymous — Mon, 16 Oct 2017 19:36:02 GMT

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

Thank you!!

Re: Set Analysis Questions - need help

Anonymous — Mon, 16 Oct 2017 19:44:54 GMT

Now that I know

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

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

Re: Set Analysis Questions - need help

pradosh_thakur — Mon, 16 Oct 2017 19:46:54 GMT

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

Re: Set Analysis Questions - need help

sunny_talwar — Mon, 16 Oct 2017 19:47:34 GMT

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

Re: Set Analysis Questions - need help

Anonymous — Mon, 16 Oct 2017 19:51:31 GMT

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.

Re: Set Analysis Questions - need help

sunny_talwar — Mon, 16 Oct 2017 19:55:00 GMT

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

4, ADJ

5, AKJH

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

Does this make sense?