Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
5Hi,
I am calculating availability for all servers. For each server availability is calculated in day wise. If the server is down the date will not be captured.
Sample:
Host Date Availability
XYZ 10/1/2015 99%
XYZ 10/2/2015 98%
XYZ 10/5/2015 96%
ABC 10/1/2015 100%
ABC 10/5/2015 98%
ABC 10/6/2015 99%
in the above example i need to generate date for XYZ host - 10/3/2015 - 0%, 10/4/2015 - 0% and for ABC host - 10/2/2015 -0%, 10/3/2015 - 0%, 10/4/2015-0%
Please help on this... how to generate this
To get 0%, create a new table and drop the main table:
Table:
LOAD * Inline [
Host, Date, Availability
XYZ, 10/1/2015, 99%
XYZ, 10/2/2015, 98%
XYZ, 10/5/2015, 96%
ABC, 10/1/2015, 100%
ABC, 10/5/2015, 98%
ABC, 10/6/2015, 99%
];
Temp:
LOAD Max(Date) as MaxDate,
Min(Date) as MinDate,
Host
Resident Table
Group By Host;
Join(Table)
LOAD Date(MinDate + IterNo() - 1) as Date,
Host
Resident Temp
While MinDate + IterNo() - 1 <= MaxDate;
FinalTable:
NoConcatenate
LOAD Host,
Date,
Num(If(IsNull(Availability), 0, Availability), '#,##0%') as Availability
Resident Table;
DROP Tables Temp, Table;
May be this:
Table:
LOAD * Inline [
Host, Date, Availability
XYZ, 10/1/2015, 99%
XYZ, 10/2/2015, 98%
XYZ, 10/5/2015, 96%
ABC, 10/1/2015, 100%
ABC, 10/5/2015, 98%
ABC, 10/6/2015, 99%
];
Temp:
LOAD Max(Date) as MaxDate,
Min(Date) as MinDate,
Host
Resident Table
Group By Host;
Join(Table)
LOAD Date(MinDate + IterNo() - 1) as Date,
Host
Resident Temp
While MinDate + IterNo() - 1 <= MaxDate;
DROP Table Temp;
Output:
To get 0%, create a new table and drop the main table:
Table:
LOAD * Inline [
Host, Date, Availability
XYZ, 10/1/2015, 99%
XYZ, 10/2/2015, 98%
XYZ, 10/5/2015, 96%
ABC, 10/1/2015, 100%
ABC, 10/5/2015, 98%
ABC, 10/6/2015, 99%
];
Temp:
LOAD Max(Date) as MaxDate,
Min(Date) as MinDate,
Host
Resident Table
Group By Host;
Join(Table)
LOAD Date(MinDate + IterNo() - 1) as Date,
Host
Resident Temp
While MinDate + IterNo() - 1 <= MaxDate;
FinalTable:
NoConcatenate
LOAD Host,
Date,
Num(If(IsNull(Availability), 0, Availability), '#,##0%') as Availability
Resident Table;
DROP Tables Temp, Table;
Hi,
I was approaching it the same way as Sunny, but as always Sunny got there much quicker.
Mark
Thanks a lot sunindia you solved my problem
No problem
I am glad I was helpful