topic Generating missing dates in QlikView

Generating missing dates

rash_611 — Mon, 16 Nov 2015 18:08:28 GMT

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

Re: Generating missing dates

sunny_talwar — Mon, 16 Nov 2015 18:17:10 GMT

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:

Re: Generating missing dates

sunny_talwar — Mon, 16 Nov 2015 18:19:47 GMT

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;

Re: Generating missing dates

Mark_Little — Mon, 16 Nov 2015 18:23:48 GMT

Hi,

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

Mark

Re: Generating missing dates

rash_611 — Mon, 16 Nov 2015 18:30:56 GMT

Thanks a lot sunindia you solved my problem

Re: Generating missing dates

sunny_talwar — Mon, 16 Nov 2015 18:32:37 GMT

No problem

I am glad I was helpful