Generating missing dates

[rash_611]

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

[sunny_talwar]

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:

[sunny_talwar · Accepted solution]

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;

[Mark_Little]

Hi,

I was approaching it the same way as Sunny, but as always Sunny got there much quicker.

Mark

[rash_611]

Thanks a lot sunindia you solved my problem

[sunny_talwar]

No problem

I am glad I was helpful