topic Re: Sections in QlikView

Sections

pgalvezt — Tue, 07 Aug 2012 13:37:14 GMT

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.

Re: Sections

Tue, 07 Aug 2012 13:43:07 GMT

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

Re: Sections

pgalvezt — Tue, 07 Aug 2012 15:48:04 GMT

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

Thanks again

Re: Sections

pgalvezt — Tue, 07 Aug 2012 16:13:38 GMT

Any Help?

Thanks

Re: Sections

chematos — Tue, 07 Aug 2012 16:57:09 GMT

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

Re: Sections

pgalvezt — Tue, 07 Aug 2012 17:41:38 GMT

Ok Im gonna to do that and let you know.

Thanks.

Re: Sections

pgalvezt — Tue, 07 Aug 2012 19:13:10 GMT

Works! Thanks

Re: Sections

chematos — Tue, 07 Aug 2012 20:24:37 GMT

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.