Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Chanty4u
MVP
MVP

RE:Convert to Sql

Hi all,

I have small  conversion on  Qlikview Expression into  SQl.

Below is my Qlikview code.   I need tis in Sql.

Count({<[USER]=e({<[NEW]={'A','B','C'}>})>}distinct [USER])/count(distinct USER)

1 Solution

Accepted Solutions
Kushal_Chawda

SELECT (SUM(a.USERCOUNTE)/SUM(a.TOTALUSER)) as USERNEW

FROM (

SELECT  COUNT(DISTINCT b.USER) as TOTALUSER,COUNT(SELECT DISTINCT USER FROM TABLE WHERE USER NOT IN ('A','B','C')) as USERCOUNTE

FROM TABLE b  )  a

UPDATE

View solution in original post

7 Replies
PrashantSangle

Create flag in script for Column New

and use it in Set analysis.

Like

Load *

If(Wildmatch(New,'A','B','C'),1,0) as flag

from table;

then your expression became

Count({<Flag={'0'}>}Distinct USER)/Count(Distinct USER)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Kushal_Chawda

SELECT (SUM(a.USERCOUNTE)/SUM(a.TOTALUSER)) as USERNEW

FROM (

SELECT  COUNT(DISTINCT b.USER) as TOTALUSER,COUNT(SELECT DISTINCT USER FROM TABLE WHERE USER NOT IN ('A','B','C')) as USERCOUNTE

FROM TABLE b  )  a

UPDATE

Chanty4u
MVP
MVP
Author

Hi Max,

i need the equivalent sql code  for testing  in db.

Not in Qlivkiew

Not applicable

select COUNT(distinct b.[USER])/count(distinct a.[USER]) from tablename a,tablenamae b

where b.[NEW]='A' or b.[NEW]='B' or b.[NEW]='C'

Chanty4u
MVP
MVP
Author

Thnx Kush.   i will try this code and let u know

Chanty4u
MVP
MVP
Author

Thnx Oza.   i will try this code and let u know

PrashantSangle

Hi,

try below

select

(select

count(t1.USER)

From table t1

where NAME NOT IN ('A','B','C')

)

/ count (t2.USER)

From table t2

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂