topic Re: Calculate day difference and possible duplicate rows in App Development

Calculate day difference and possible duplicate rows

danimelo1 — Sat, 16 Nov 2024 06:05:39 GMT

Hi Experts.

I am trying to calculate the difference between two dates (a date and a dateTimeStamp).

-When I sum the dimension, it is not doing as I am specting: Days = Sum(DIAS_INSTALACION)

The script editor:

Ventas:
First 52000
LOAD
PEDIDO_ID,
Date(Floor(TimeStamp(FECHA_INSTALACION,'DD/MM/YYYY hh:mm:ss')), 'DD/MM/YYYY') as FECHA_INSTALACION,
FECHA_CITA

FROM [lib://Datos/VENTAS_TBL.qvd]
(qvd);

left join
LOAD
PEDIDO_ID,
Num(FECHA_CITA-FECHA_INSTALACION) as DIAS_INSTALACION
Resident Ventas;

Am I missing something?

Re: Calculate day difference and possible duplicate rows

vvira1316 — Thu, 11 Apr 2019 13:22:56 GMT

Hi,

Will you be able to share masked data and or sample QVF file?

Re: Calculate day difference and possible duplicate rows

Vegar — Thu, 11 Apr 2019 13:39:36 GMT

What happens if you use measure SUM(DIAS_INSTALACION) instead of the dimension DIAS_INSTALACIONin your chart?

I notice you are joining the data back into your Ventas table, if you hav duplicate PEDIDO_ID then your join will create duplicate rows. You could try this approach instead.

Ventas:
LOAD 
  PEDIDO_ID, 
  FECHA_CITA,  
  FECHA_INSTALACION,
  Num(FECHA_CITA-FECHA_INSTALACION) as DIAS_INSTALACION 
;
LOAD
   PEDIDO_ID,
   FECHA_CITA,
   dayname(TimeStamp(FECHA_INSTALACION,'DD/MM/YYYY hh:mm:ss')) as FECHA_INSTALACION  
FROM 
   [lib://Datos/VENTAS_TBL.qvd] (qvd)
;

Re: Calculate day difference and possible duplicate rows

danimelo1 — Thu, 11 Apr 2019 14:14:17 GMT

I'm attaching an example.

Re: Calculate day difference and possible duplicate rows

Vegar — Thu, 11 Apr 2019 14:57:03 GMT

As you see in the screenshot below, you have duplicate PEDITO_ID, which leads to the sum(DIAS_INSTALATCION) will be wrong, when joining a table without a unique primary key you could get duplicate rows. I think you should consider my preceding load solution in my earlier answer.

Re: Calculate day difference and possible duplicate rows

danimelo1 — Thu, 11 Apr 2019 18:18:56 GMT

Thanks Vegar. The only thing is that it takes more time.

Re: Calculate day difference and possible duplicate rows

Vegar — Thu, 11 Apr 2019 19:59:24 GMT

If you still want to use join then you need to find or create an unique identifier to the tables so you are not joining duplicates. If the data does not provide you could manually as to who() to your master Ventas table.