sql condition in qlikview

[#1]

Hi Experts,

i have the following sql query to calculate two fields.

sum(case when [Customer Number] ='UNKNOWN' then 0 else 1 end) as [Number Of Customers],

count(distinct case when [Customer Number] ='UNKNOWN' then 

concat(

[Date Of Birth],

[Gender],

[Customer Address Line 1],

[Customer Address Line 2],

[Customer AddressLine 3],

[Customer Address Line 5],

[Customer Postcode],

[Customer Country]) end)

How can i convert this to qlikview expression?

Please someone help me with the exact expression

[sunny_talwar]

May be like this

Sum(If([Customer Number] = 'UNKNOWN', 0, 1) as [Number Of Customers],

Not so sure what the second statement is doing

[#3]

sunny,

when i tried 'Sum(If([Customer Number] = 'UNKNOWN', 0, 1) as [Number Of Customers],' inthe script , i m getting error as 'Nested aggregation not allowed'

Please help

[sunny_talwar]

Did you add a group by statement? Or may be all you want to do is create a flag and not do the sum

If([Customer Number] = 'UNKNOWN', 0, 1)

Also, I missed a parenthesis at the end in my last script line. I hope you added it before you ran it?

Sum(If([Customer Number] = 'UNKNOWN', 0, 1)) as [Number Of Customers],

[#5]

sunny,

i have added the paranthesis & groupby before running.

still error

[sunny_talwar]

Can you share your script and a screenshot of the error?

[Peter_Cammaert]

It's never a good idea to try to translate 1:1 a (complex) SQL statement into a seemingly equivalent QlikView expression (User Interface only, right?). There is a better technique to get the same output from an expression: get the business rule behind these SQL queries. What do they intend to do? If you can describe that in plain text, an optimal QlikView expression will be easy to construct, will be easier to maintain, will be better performing, and last buyt not least - since you already described the business logic - it will be documented from the start.

Can you describe what this code does?