6 Replies Latest reply: Apr 23, 2012 5:40 PM by Sebastián Pereira RSS

    how to delete months without data

    gerardo.pauza

      Hi everyone,

              as you can see in the image below, the table "suscriptos activos" has values in ene-2010 (january-2010), feb-2010 and mar-2010, but this is not truth, because the first value of the table behing (subscriptions.created) start over nov-2010 (5/11/2010).

      table.png

      here is the syntax of "suscriptos activos":

      SUM( IF( SUBSCRIPTIONS.is_subscribed = 0 , IF ( SUBSCRIPTIONS.modified > CALENDARIO_SUBSCRIPTIONS.AñoMes , 1 , 0 ), 1 ) )

       

      Can i add same function to avoid the months without data?

       

      Syntax of "subscriptions.crated"

      CALENDARIO_SUBSCRIPTIONS:

      LOAD

         Year(PeriodDate) & Num(Month(PeriodDate),'00') & Num(Day(PeriodDate),'00')    as %Key_CREATED_SUBSCRIPTIONS,

         Year(PeriodDate)                                                         as Año,

         Month(PeriodDate)                                                        as Mes,

         Day(PeriodDate)                                                            as Dia,

         PeriodDate                                                                 as Fecha,

         DUAL(Month(PeriodDate) & '-' & Year(PeriodDate),

              Year(PeriodDate) & Num(Month(PeriodDate),'00'))                        as AñoMes,

         Week(PeriodDate)                                                            as Semana,

         Ceil(Month(PeriodDate)/3)                                                as Trimestre,

         (Year(PeriodDate) * 12) + Num(Month(PeriodDate))                            as LineaTemporal

      RESIDENT TempCalendar;

       

      Syntax of "subscriptions table":

      SUBSCRIPTIONS:

      LOAD

          Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_CREATED_SUBSCRIPTIONS,

          city_id,

          city_id as %Key_CITY_SUBSCRIPTIONS_ID,

          email,

          email as %Key_EMAIL,

          is_voluntary,

          is_subscribed,

          modified,

          user_id,

          created,

          /*user_id as %Key_USER_ID,*/

          id as %Key_SUBSCRIPTION_ID

      FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd)

       

      thanks!