topic Re: Calculate interval between dates with condition in QlikView

Calculate interval between dates with condition

Fri, 17 Oct 2014 18:55:40 GMT

Hi,

I am new with Qlik Sense and I am trying to calculate interval between dates with condition.

I have two tables with a same ID (IDEmb):

IDEmb	Date	PT
CPX01	17/01/2012	CPX
CPX02	21/08/2012	CPX
CPX03	15/01/2013	CPX
CPX04	06/11/2013	CPX
CPX05	01/04/2014	CPX
CPX06	12/06/2014	CPX
PPK01	06/11/2013	PPK
PPK02	01/04/2014	PPK
PPK03	12/06/2014	PPK

IDEmb	Manu	Type
CPX01	AA	Meteo
CPX01	BB	Meteo
CPX01	DD	N/A
CPX02	BB	Meteo
CPX03	CC	Ocean
CPX04	BB	Meteo
CPX05	BB	Meteo
CPX06	AA	Meteo
CPX06	CC	Ocean
PPK01	AA	Ocean
PPK02	BB	Ocean
PPK02	CC	Meteo
PPK02	AA	Ocean
PPK02	TT	Meteo
PPK03	AA	Ocean
PPK03	BB	Meteo
PPK03	EE	Ocean

I would like to have the interval between the dates base on the Type and PT like the following tables:

IDEmb	Date	PT	Type	Interval
CPX01	17/01/2012	CPX	Meteo	-
CPX02	21/08/2012	CPX	Meteo	217
CPX04	06/11/2013	CPX	Meteo	442
CPX05	01/04/2014	CPX	Meteo	146
CPX06	12/06/2014	CPX	Meteo	72

IDEmb	Date	PT	Type	Interval
CPX03	15/01/2013	CPX	Ocean	-
CPX06	12/06/2014	CPX	Ocean	513

IDEmb	Date	PT	Type	Interval
PPK02	01/04/2014	PPK	Meteo	-
PPK03	12/06/2014	PPK	Meteo	72

IDEmb	Date	PT	Type	Interval
PPK01	06/11/2013	PPK	Ocean	-
PPK02	01/04/2014	PPK	Meteo	146
PPK03	12/06/2014	PPK	Meteo	72

I have managed to do a graph but I can’t calculate the avg, min, max.

I used the expression:

if(only({<Type = {'Meteo'}>}Emb),Emb-above(total Emb),0) but is using the date above and not the date with Type = {'Meteo'} and the avg, min, max don’t work

I have tried to create new table when II import the data but is not working.

Thanks,

Patrice

Re: Calculate interval between dates with condition

Gysbert_Wassenaar — Fri, 17 Oct 2014 19:41:46 GMT

See attached qvw

Re: Calculate interval between dates with condition

Sun, 19 Oct 2014 00:49:47 GMT

Hi Gysbert,

if(above(total PT)=PT and above(total Type) = Type, Date-above(total Date),0)

The expression doesn't work properly. The PT condition is perfect but the Type condition don't work. So every value is 0. I try to change a bit the expression but it was not successful.

I think that the problem is because you can have several type of "Type" in the same "IDEmb".

Any idee to resolve this?

I will try (monday) to create a new colunm when I import the data with this expression [if(Previous(total PT)=PT , Date-Previous(total Date),0) and use a sum() in mesure.

Thanks

Patrice

Re: Calculate interval between dates with condition

Mon, 03 Nov 2014 15:31:12 GMT

Hi,

I resolve the problem with a JOIN during "load data":

MOcea:

LOAD DISTINCT

IDEmb,

Type as TypeOcea

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is M)

WHERE Type = 'Ocean';

LEFT JOIN (MOcean)

LOAD

IDEmb,

PT as PTOcean,

Date as DateOcean

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is Emb);

LEFT JOIN (MOcea)

LOAD

IDEmb,

If(Previous(PTOcean)=PTOcean, DateOcean-Previous(DateOcean)) as IOcea

RESIDENT MOcea;

DROP FIELD DateOcean, PTOcea;

MMeteo:

LOAD DISTINCT

IDEmb,

Type as TypeMeteo

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is M)

WHERE Type = 'Meteo';

LEFT JOIN (MMeteo)

LOAD

IDEmb,

PT as PTMeteo,

Date as DateMeteo

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is Emb);

LEFT JOIN (MOcea)

LOAD

IDEmb,

If(Previous(PTMeteo)=PTMeteo, DateMeteo-Previous(DateMeteo)) as IOcea

RESIDENT MOcea;

DROP FIELD DateMeteo, PTMeteo;

Patrice