Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
can you please mark the correct answer and close this thread , so that it will be helpful other
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;
please find the attachment for the solution
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.
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;
Thanks its working
can you please mark the correct answer and close this thread , so that it will be helpful other
Hi Binod,
What if Status UP record missed? Did you tested this scenario?
Regards,
Jagan.
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"
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.