Count days between - MinDate/MaxDate (Load script)

[varmekontrol]

HI

Can anyone help me figure out how to get a MinDate and a MaxDate. Then count the days between, and assosiate with the column MeterNO

DaysWithZero:
LOAD
    MeterNO,
    Date("StatusDate") as StatusDate
FROM [lib://Desktop/ZeroConsumption.xls]
(biff, embedded labels, table is Export$);

Temp2: 
Load
 MeterNO,
    StatusDato,
    Min(StatusDate) - Max(StatusDate) as DaysWithZero     
Resident DaysWithZero;

Drop Table DaysWithZero;

I have one table "ZeroConsumption.xls"

MeterNo	StatusDate
111222333	01/01/2018
111222333	05/01/2018
111222333	07/01/2018

I want a table with MeterNo,DaysBetweenStatusDate. (min/max dates)

In this case

MeterNo	DaysBetweenMin/Max
111222333	6

[sunny_talwar · Accepted solution]

May be try this

DaysWithZero:

LOAD MeterNO,

     Date("StatusDate") as StatusDate

FROM [lib://Desktop/ZeroConsumption.xls]

(biff, embedded labels, table is Export$);

Left Join (DaysWithZero)

Load MeterNO,

     Min(StatusDate) - Max(StatusDate) as DaysWithZero  

Resident DaysWithZero

Group By MeterNO;

Drop Table DaysWithZero;

[OmarBenSalem]

U have it right, u just need to group by to achieve ur goal:

Table:

load MeterNo,date(date#( StatusDate,'DD/MM/YYYY') ) as StatusDate Inline [

MeterNo, StatusDate

1, 01/01/2018

1, 05/01/2018

1 ,07/01/2018

2,01/01/2017

2,02/06/2017

2,05/07/2017

2,06/08/2017

];

load MeterNo,

Max(StatusDate)-min(StatusDate) as DaysBetweenMinMax Resident Table Group by MeterNo;

result:

[varmekontrol]

Both were correct, so thank you.

Can only give one correct answer