Need help in generating Rows in Load Script

[#1]

Hi Team,

As per the requirement i need to generate a row in the table. For a any given server on a given date there will always be on record in with UP status and if the server is down on that then only one record with DOWN status with be inserted in table its duration.

Date	Server	Status	Duration
21/09/2015	A	UP	12
21/09/2015	B	UP	112
21/09/2015	B	DOWN	78
22/09/2015	A	UP	32
22/09/2015	A	DOWN	32
22/09	B	UP	455

In the above table Server A has only UP status on 21/09/2015 , So we need to generate a new row with DOWN Status with duration as 0.

The expected output will be

Date	Server	Status	Duration
21/09/2015	A	UP	12
21/09/2015	A	DOWN	0
21/09/2015	B	UP	112
21/09/2015	B	DOWN	78
22/09/2015	A	UP	32
22/09/2015	A	DOWN	32
22/09	B	UP	455
22/09	B	DOWN	0

Please help hic‌jontydkpi‌@

[swuehl]

Maybe like this:

INPUT:
LOAD * INLINE [
Date, Server, Status, Duration
21/09/2015, A, UP, 12
21/09/2015, B, UP, 112
21/09/2015, B, DOWN, 78
22/09/2015, A, UP, 32
22/09/2015, A, DOWN, 32
22/09/2015, B, UP, 455
];


COMB:
LOAD DISTINCT Date Resident INPUT;


JOIN
LOAD DISTINCT Server RESIDENT INPUT;


JOIN
LOAD Distinct Status Resident INPUT;


LEFT JOIN (COMB)
LOAD * Resident INPUT;


DROP TABLE INPUT;


RESULT:
NoConcatenate LOAD Date, Server, Status, Alt(Duration,0) as Duration
Resident COMB
ORDER BY Date, Server, Status desc;


DROP Table COMB;

[avinashelite]

please find the attachment for the solution

[jagan]

Hi,

Try like this

Data:

LOAD Date,

     Server,

     Status,

     Duration

FROM

[sample data 2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

NoConcatenate

LOAD

  Date,

  Server,

  0 AS Duration

RESIDENT Data;

LEFT JOIN(Temp)

LOAD

*

INLINE [

Status

UP

DOWN];

Concatenate(Data)

LOAD

*

RESIDENT Temp;

DROP TABLE Temp;

Final:

NoConcatenate

LOAD

  Date,

     Server,

     Status,

     Sum(Duration) AS Duration

RESIDENT Data

GROUP BY Date,

     Server,

     Status;

   DROP TABLE Data;

Regards,

Jagan.

[avinashelite]

T:

LOAD Date,

     Server,

     Status,

     Duration

FROM

[https://community.qlik.com/thread/186121]

(html, codepage is 1252, embedded labels, table is @1);

T1:

LOAD Date as D,

Server as S,

count(Status) as Count_status

Resident

T

group by

Date, Server;

Concatenate(T)

LOAD D as Date,

  S as Server,

  'DOWN' as Status,

  0 as Duration

  Resident

  T1

  where Count_status=1;

  Drop Table T1;

[#6]

Thanks its working

[avinashelite · Accepted solution]

can you please mark the correct answer and close this thread , so that it will be helpful other

[jagan]

Hi Binod,

What if Status UP record missed?  Did you tested this scenario?

Regards,

Jagan.

[avinashelite]

Hi Jagan,

My solution is based on this statement below

" there will always be on record in with UP status and if the server is down on that then only one record with DOWN status"

[#10]

Hi Jagan and avinash,

Yes there will be always one record with UP for a server on a given date . Only if the server is down on that day then a two records will be inserted in the table one with UP and DOWN.