SQL Code Conversion to QlikView

[#1]

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

[jonathandienst]

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

[#3]

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.

[#4]

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