topic Re: SQL Code Conversion to QlikView in QlikView

SQL Code Conversion to QlikView

Wed, 26 Sep 2012 14:34:04 GMT

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

Re: SQL Code Conversion to QlikView

jonathandienst — Wed, 26 Sep 2012 14:42:38 GMT

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

Re: SQL Code Conversion to QlikView

Wed, 26 Sep 2012 14:47:11 GMT

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.

Re: SQL Code Conversion to QlikView

Thu, 27 Sep 2012 18:11:23 GMT

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