Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
danimelo1
Contributor

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?

1 Solution

Accepted Solutions
Partner
Partner

Re: Calculate day difference and possible duplicate rows

 

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)
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
6 Replies
vvira1316
Valued Contributor II

Re: Calculate day difference and possible duplicate rows

Hi,

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

Partner
Partner

Re: Calculate day difference and possible duplicate rows

 

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)
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
danimelo1
Contributor

Re: Calculate day difference and possible duplicate rows

I'm attaching an example.

Partner
Partner

Re: Calculate day difference and possible duplicate rows

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
danimelo1
Contributor

Re: Calculate day difference and possible duplicate rows

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

Re: Calculate day difference and possible duplicate rows

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.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes