Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

SQL Code Conversion to QlikView

Hi All,

I am currently converting some of our business's SQL have created. One of the case statement is not returning correct result from database.

LOAD

........

....

SQL SELECT

....

...

,Case when (em.elapsed_months >=6 and em.communications >=6) then 1 else  0 end as M6_Non_Engager

,case when em.elapsed_months >=4 then 1 else 0 end   as M4_Non_Engager

,case when em.elapsed_months <=4 then 1 else 0 end as  Engaged

.....

// Following is the case statement which is returning 'No Value' always but in database actually there are all values.

,case when dm.score  = 1 then 'Low Email Tech Laggards'

     when dm.score  = 2 then 'Low Email Gamers'

     when dm.score  = 3 then 'Moderates, without Email'

     when dm.score  = 4 then 'Moderates, with Email'

     when dm.score  = 5 then 'App & Email Gamers'

     when dm.score  = 6 then 'Avid Mobile Web & BBM'

     when dm.score  = 7 then 'Super Users'

     else 'No Value' end as Behavioral_Segment

.......

, count(distinct ld.e_email) as Countx

Please help

3 Replies
MVP
MVP

Re: SQL Code Conversion to QlikView

Hi

The case statement looks fine to me. Either there are no records with a dm.score value in the range 1-7, or whatever filter you have on your SQL statement is excluding all of them. I suggest that you add dm.score to your query so you can see what is returned.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: SQL Code Conversion to QlikView

Thanks Jonathan fo reply. I checked there are multiple records for each value 1-7. If I add dm.score field it shows only "-" in possible values.

Not applicable

Re: SQL Code Conversion to QlikView

Problem is solved.

There was no problem with case statement.

It was a problem with condition in joining of table. I was using the following condition:

dm.snapshot_date = '31-JUL-2012'

Based on above condition it was not joining the table. I replaced it with following:

dm.snapshot_date = TO_DATE('31-JUL-2012','DD-MON-YYYY')

Community Browser