Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danimelo1
Creator
Creator

Calculate day difference and possible duplicate rows

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)

 

Days.png

 

 

 

 

 

 

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?

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

 

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

 

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

View solution in original post

6 Replies
vvira1316
Specialist II
Specialist II

Hi,

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

Vegar
MVP
MVP

 

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

 

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)
;
danimelo1
Creator
Creator
Author

I'm attaching an example.

Vegar
MVP
MVP

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. 

image.png

danimelo1
Creator
Creator
Author

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

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.