topic Re: sql condition in qlikview in QlikView

sql condition in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: sql condition in qlikview

sunny_talwar — Fri, 21 Oct 2016 13:18:47 GMT

May be like this

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

Not so sure what the second statement is doing

Re: sql condition in qlikview

Fri, 21 Oct 2016 14:02:28 GMT

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

Re: sql condition in qlikview

sunny_talwar — Fri, 21 Oct 2016 14:04:44 GMT

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],

Re: sql condition in qlikview

Fri, 21 Oct 2016 14:06:25 GMT

sunny,

i have added the paranthesis & groupby before running.

still error

Re: sql condition in qlikview

sunny_talwar — Fri, 21 Oct 2016 14:08:12 GMT

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

Re: sql condition in qlikview

Peter_Cammaert — Fri, 21 Oct 2016 14:27:12 GMT

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?