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!