Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to delete months without data

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!

6 Replies
sebastiandperei
Specialist
Specialist

Mh... no entendí. podés mandar el qvw?

Nicole-Smith

You can use a where statement to avoid this data.  Consider the following (I have also attached a .qvw):

Table:

LOAD * INLINE [

Date, Number

4/1/12, 1

4/3/12,

4/15/12, 15

4/23/12, 23

]

WHERE Number <> '' AND Number <> NULL();

This will return a table that looks like this:

DateNumber
4/1/121
4/15/1215
4/23/1223

Notice that the date of 4/3/12 was avoided since it doesn't contain any data.

Not applicable
Author

Hi Nicole,

     thanks for your answer but the thing is i have no values for this months.

I attached the .qvw

As you will see in the attached file the table1 start to show values from nov-2010, but in table 2 there are values from ene-2010

Thanks for any advice

Gerardo

Nicole-Smith

The problem is with the expression on the second chart.  See the attached .qvw with a different expression.  Here it doesn't show the months that don't have data.  If you need help writing a different expression, I will need further explanation on what exactly you are trying to calculate.

sebastiandperei
Specialist
Specialist

En primer lugar, en la expresión hay un error en uno de los campos, pusiste SUBSCRIPTIONS_modified y creo que sería SUBSCRIPTIONS_AñoMes_modified.

Comentame, cómo es la cuenta? porque no entiendo la expresiòn.

sebastiandperei
Specialist
Specialist

No sabìa que estaba respondido. Sorry