Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
| |
111222333 |
|
I want a table with MeterNo,DaysBetweenStatusDate. (min/max dates)
In this case
MeterNo | DaysBetweenMin/Max |
---|---|
111222333 | 6 |
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;
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;
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:
Both were correct, so thank you.
Can only give one correct answer