Sections

[pgalvezt]

Hello,

I have many databases with the same columns importants. 2 of them contain dates like debt date (for month... in this case May) the other column contains endmonth(31/05/2012). Extracting debt Days I do the next 31/05/2012 - Debt date (can be 01 until 20 of may) example 31/05/2012 - 15/05/2012 = 16 days.

I have a new column with many days 1,2,3,4,5,6,7,etc.

I need to do a stratification with thouse days. When the debt date = endMonth (No debt) 41060 = 31/05/2012

Estratificacion:

Load

If(DiasMora = 41060,                  Dual('No debt',1),

If(DiasMora>=1  And   DiasMora <=30,  Dual('1-30 Días',2),

If(DiasMora>=31  And  DiasMora <=60,  Dual('31-60 Días',3),

if(DiasMora>=31  And  DiasMora<=60,   Dual('31-60 Días',3),

if(DiasMora>=61  And  DiasMora<=90,   Dual('61-90 Días',4),

if(DiasMora>=91  And  DiasMora<=120,  Dual('91-120 Días',5),

if(DiasMora>=121 And  DiasMora<=150,  Dual('121-150 Días',6),

if(DiasMora>=151 And  DiasMora<=180,  Dual('151-180 Días',7),

if(DiasMora>=181 And  DiasMora<=210,  Dual('181-210 Días',8)))))))))) as Estratificacion

When I create the tables (Dimenssion: Estratificacion)  (expression Total_Debt)

Appears:

No debt 10000

1-30      10000

31-60    10000

10000 is the total.

How to get the real stratification. This work if I put this formula with aggr in the expression. But QlikView its getting too slow. I need this work into the script.

Thank you.

[#2]

Use Interval Match in your load script to bucket your items rather than an if statement then just use the new item as a dimension

[pgalvezt]

Thank you for your reply. Could you please put an example with my intervals.

Thanks again

[pgalvezt]

Any Help?

Thanks

[chematos]

Intervalmatch should works:

TABLE:

LOAD

      ID,

     Descripcion,

     Desde,     //since

     Hasta      //to

FROM

tableX;

If you don´t have the intervals in a table you must create it with an Inline or write it in an excel to read it after,

if you  make an inline would be like this:

TABLE:

Load inline

[ ID, Descripcion, Desde, Hasta

  1,  'No debt', 0, 0

   2, '1-30', 1 , 30

......

...

...

];

INNER JOIN INTERVALMATCH(DiasMora)

LOAD

           Desde,

          Hasta

RESIDENT TABLE;

I you need to make calculation you can do it grouping by the ID

[pgalvezt]

Ok Im gonna to do that and let you know.

Thanks.

[pgalvezt]

Works! Thanks

[chematos · Accepted solution]

INNER JOIN INTERVALMATCH(DiasMora)

LOAD

Descripción

RESIDENT Estratificacion;

eso deberia ser así:

INNER JOIN INTERVALMATCH(DiasMora)

LOAD

Inicio,

Fin

RESIDENT Estratificacion;

En la tabla de estratificación debes tener un ID para identificar cada rango que quieras, un INICIO, un FIN y la DESCRIPCION de cada rango.

Luego puedes usar DiasMora con el intervalmatch que te he puesto y si quieres agregar otro campo junto con DiasMora, puedes usar el intervalmatch así:

INNER JOIN INTERVALMATCH(DiasMora, CampoValor)

LOAD

Inicio,

Fin

RESIDENT Estratificacion;

así tendrás el valor para hacer el sum() asociado con cada ID de cada rango, sino, tienes que hacer un left join o relacionar el ID de Estratificacion con la tabla donde tengas los valores que quieres sumar para calcular los totales.