topic Re: call first date in QlikView

call first date

pgalvezt — Mon, 04 Dec 2017 16:13:38 GMT

Hello,

I Have created a pivot table with many products, each products has different amount dates. I need to left always the first day product...

In the example (of course there are many products in the table) I have:

Products 038 and 043 with many dates. I would like to show just the older date.

So for 038 Product 06/06/2017

Product 043 05/07/2017.

Hope can help me with this,

Thanks!

Re: call first date

Anonymous — Mon, 04 Dec 2017 16:36:35 GMT

Hello Pablo,

one of the options is that you calculate el first date for product in the script and create a flag for you identify what is the date

in the script:

left join(Temp_CceT_Movimientos)

load min(MovCceCalFecha1) as MovCceCalFecha1,

MovCceGlosa,

1 as _flagFirstDate

resident Temp_CceT_Movimientos

group by MovCceGlosa

in the layout

Sum({$<_flagFirstDate = {1}>}MovCceMontoLocalDebe - MovCceMontoLocalHaber)

Re: call first date

pgalvezt — Tue, 05 Dec 2017 13:39:47 GMT

Hello Diego, This message appear in QlikView (ERROR). I think its take a Min function as invalid expression...

Invalid expression

Left Join( Temp_CceT_Movimientos)

LOAD

CabCompId,

MovCceMontoLocalDebe,

MovCceMontoLocalHaber,

Saldo,

MovCceGlosa,

CtaId,

min(MovCceCalFecha1) as MovCceCalFecha1,

1 as _flagFirstDate,

Floor(Min(MovCceConFecha)) as MovCceCalFecha3,

MovCceFecVenc,

MovCceDigFec

Resident Temp_CceT_Movimientos

group by MovCceGlosa

Re: call first date

pgalvezt — Wed, 06 Dec 2017 15:22:35 GMT

Anyone can you help me with this please?

Thanks!

Re: call first date

Anonymous — Wed, 06 Dec 2017 15:41:12 GMT

Hi Pablo,

the problem is that the expression should have other additional fields

LOAD

CabCompId,

MovCceMontoLocalDebe,

MovCceMontoLocalHaber,

Saldo,

MovCceGlosa,

CtaId,

min(MovCceCalFecha1) as MovCceCalFecha1,

1 as _flagFirstDate,

Floor(Min(MovCceConFecha)) as MovCceCalFecha3,

MovCceFecVenc,

MovCceDigFec

Resident Temp_CceT_Movimientos

group by

MovCceMontoLocalDebe,

MovCceMontoLocalHaber,

Saldo,

MovCceGlosa,

CtaId,

MovCceFecVenc,

MovCceDigFec;

Re: call first date

pgalvezt — Wed, 06 Dec 2017 18:07:55 GMT

Hello,

I attached the resut with the changes. Still giving me the same result...

My table has to show just 2 dates

For example if you do clic on c17/038 qlikview will show you 3 dates just I need the older and so on.... !

06/06/2017 c17/038 Range 121 days and

05/07/2017 c17/043... Range 91-120 Days

Re: call first date

maxgro — Wed, 06 Dec 2017 18:41:29 GMT

this?

=aggr(Min(MovCceCalFecha1), MovCceGlosa_Homologacion)

Re: call first date

Anonymous — Wed, 06 Dec 2017 19:35:50 GMT

ok, you don't can add all the fields, should add only the field call MovCceGlosa_Homologacion

Re: call first date

pgalvezt — Thu, 07 Dec 2017 14:03:33 GMT

Hello Massimo,

Thank you for your reply,

The first table works perfect, but range still count 5 dates, can you help me with that?

Output must be:

06/06/2017 c17/038 Range 121 days and

05/07/2017 c17/043... Range 91-120 Days

RANGE COUNT Value

Range 121 1 12207

Range 91-120 Days 1 15775

Re: call first date

maxgro — Fri, 08 Dec 2017 11:12:31 GMT

I added the range as an expression, result in image

=If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=30,'0-30 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=31 and '$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=60,'31-60 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=61 and '$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=90,'61-90 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=91 and '$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=120,'91-120 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=121, '121 Días')))))