topic Re: how to delete months without data in QlikView

how to delete months without data

Fri, 20 Apr 2012 16:22:33 GMT

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

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!

how to delete months without data

sebastiandperei — Mon, 23 Apr 2012 13:44:40 GMT

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

Re: how to delete months without data

Nicole-Smith — Mon, 23 Apr 2012 13:44:47 GMT

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:

Date	Number
4/1/12	1
4/15/12	15
4/23/12	23

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

Re: how to delete months without data

Mon, 23 Apr 2012 16:22:30 GMT

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

Re: how to delete months without data

Nicole-Smith — Mon, 23 Apr 2012 18:21:43 GMT

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.

Re: how to delete months without data

sebastiandperei — Mon, 23 Apr 2012 21:38:46 GMT

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.

Re: how to delete months without data

sebastiandperei — Mon, 23 Apr 2012 21:40:31 GMT

No sabìa que estaba respondido. Sorry