Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
DEAR TEAM,
IS THERE ANY WAY BY WHICH DATE CAN BE MAPPED WITH 0-23 hours and then do the logic based on HOUR filter.?
CAN YOU PLEASE SUGGEST ?
INPUT FILE:
AlarmDate | Alarm ID | Technology | Vendor | RNC | NodeB | Cell | NE Level | Raised Time | Dropped Time | Severity |
17-02-2017 | 1483992308686 | UMTS | ZTE | R1 | S1 | NODEB | 17-02-2017 15:16 | 17-02-2017 18:16 | Critical | |
17-02-2017 | 1483992308685 | UMTS | ZTE | R2 | S1 | NODEB | 17-02-2017 15:16 | 17-02-2017 18:16 | Critical | |
17-02-2017 | 1483992308684 | UMTS | ZTE | R3 | S1 | NODEB | 17-02-2017 15:16 | Critical | ||
17-02-2017 | 1483992308683 | UMTS | ZTE | R4 | S1 | EKOL0000JGPA3 | CELL | 17-02-2017 15:08 | 17-02-2017 18:08 | Critical |
17-02-2017 | 1483992308682 | UMTS | ZTE | R5 | S1 | EKOL0000JGPA2 | CELL | 17-02-2017 15:08 | 17-02-2017 18:08 | Critical |
17-02-2017 | 1483992308680 | UMTS | ZTE | R6 | S1 | EKOL0000JGPA1 | CELL | 17-02-2017 15:08 | Critical | |
17-02-2017 | 1483992308679 | UMTS | ZTE | R7 | S1 | EKOL0000BOND3 | CELL | 17-02-2017 14:54 | Critical | |
17-02-2017 | 1483992308678 | UMTS | ZTE | R8 | S1 | EKOL0000BOND2 | CELL | 17-02-2017 14:54 | Critical | |
17-02-2017 | 1483992308673 | UMTS | ZTE | R9 | S1 | NODEB | 17-02-2017 14:45 | Critical | ||
17-02-2017 | 1483992308672 | UMTS | ZTE | R10 | S1 | NODEB | 17-02-2017 10:38 | Critical | ||
17-02-2017 | 1483992303238 | UMTS | ZTE | R11 | S1 | NODEB | 17-02-2017 15:13 | 17-02-2017 18:13 | Major | |
17-02-2017 | 1483992267154 | UMTS | ZTE | R12 | S1 | NODEB | 17-02-2017 15:14 | 17-02-2017 18:14 | Major | |
17-02-2017 | 1483992267153 | UMTS | ZTE | R13 | S1 | NODEB | 17-02-2017 15:14 | Minor | ||
17-02-2017 | 1483992267147 | UMTS | ZTE | R14 | S1 | NODEB | 17-02-2017 15:14 | Major | ||
17-02-2017 | 1483992267144 | UMTS | ZTE | R15 | S1 | NODEB | 17-02-2017 15:14 | Minor | ||
17-02-2017 | 1483992267143 | UMTS | ZTE | R16 | S1 | NODEB | 17-02-2017 15:13 | Major | ||
17-02-2017 | 1483992267142 | UMTS | ZTE | R17 | S1 | NODEB | 17-02-2017 15:13 | Minor | ||
17-02-2017 | 1483992267134 | UMTS | ZTE | R18 | S1 | NODEB | 17-02-2017 15:12 | Minor | ||
17-02-2017 | 1483992267133 | UMTS | ZTE | R19 | S1 | NODEB | 17-02-2017 15:12 | Minor | ||
17-02-2017 | 1483992267132 | UMTS | ZTE | R20 | S1 | NODEB | 17-02-2017 15:12 | Minor | ||
17-02-2017 | 1483992308686 | UMTS | ZTE | R21 | S1 | NODEB | 17-02-2017 15:16 | 17-02-2017 18:16 | Critical | |
17-02-2017 | 1483992308685 | UMTS | ZTE | R22 | S1 | NODEB | 17-02-2017 15:16 | 17-02-2017 18:16 | Critical | |
17-02-2017 | 1483992308684 | UMTS | ZTE | R23 | S1 | NODEB | 17-02-2017 15:16 | Critical | ||
17-02-2017 | 1483992308683 | UMTS | ZTE | R24 | S1 | EKOL0000JGPA3 | CELL | 17-02-2017 15:08 | 17-02-2017 18:08 | Critical |
17-02-2017 | 1483992308682 | UMTS | ZTE | R25 | S1 | EKOL0000JGPA2 | CELL | 17-02-2017 15:08 | 17-02-2017 18:08 | Critical |
17-02-2017 | 1483992308680 | UMTS | ZTE | R26 | S1 | EKOL0000JGPA1 | CELL | 17-02-2017 15:08 | 17-02-2017 21:08 | Critical |
17-02-2017 | 1483992308679 | UMTS | ZTE | R27 | S1 | EKOL0000BOND3 | CELL | 17-02-2017 14:54 | 17-02-2017 20:54 | Critical |
17-02-2017 | 1483992308678 | UMTS | ZTE | R28 | S1 | EKOL0000BOND2 | CELL | 17-02-2017 14:54 | Critical | |
17-02-2017 | 1483992308673 | UMTS | ZTE | R29 | S1 | NODEB | 17-02-2017 14:45 | Critical | ||
17-02-2017 | 1483992308672 | UMTS | ZTE | R30 | S1 | NODEB | 17-02-2017 10:38 | Critical | ||
17-02-2017 | 1483992303238 | UMTS | ZTE | R31 | S1 | NODEB | 17-02-2017 15:13 | 17-02-2017 18:13 | Major | |
17-02-2017 | 1483992267154 | UMTS | ZTE | R32 | S1 | NODEB | 17-02-2017 15:14 | 17-02-2017 18:14 | Major | |
17-02-2017 | 1483992267153 | UMTS | ZTE | R33 | S1 | NODEB | 17-02-2017 15:14 | 17-02-2017 21:14 | Minor | |
17-02-2017 | 1483992267147 | UMTS | ZTE | R34 | S1 | NODEB | 17-02-2017 15:14 | 17-02-2017 21:14 | Major | |
17-02-2017 | 1483992267144 | UMTS | ZTE | R35 | S1 | NODEB | 17-02-2017 15:14 | Minor | ||
17-02-2017 | 1483992267143 | UMTS | ZTE | R36 | S1 | NODEB | 17-02-2017 15:13 | Major | ||
17-02-2017 | 1483992267142 | UMTS | ZTE | R37 | S1 | NODEB | 17-02-2017 15:13 | Minor | ||
17-02-2017 | 1483992267134 | UMTS | ZTE | R38 | S1 | NODEB | 17-02-2017 15:12 | Minor | ||
17-02-2017 | 1483992267133 | UMTS | ZTE | R39 | S1 | NODEB | 17-02-2017 15:12 | Minor | ||
17-02-2017 | 1483992267132 | UMTS | ZTE | R40 | S1 | NODEB | 17-02-2017 15:12 | Minor |
Based on above table structure there is requirement to get COUNT(ALARMID) on "Hour basis"" and also DAILY basis.
So output table requirement is :
Resolution | Date | Hour | CRITICAL | MAJOR | MINOR | WARNING |
HOURLY | 17-02-2017 | 0 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 1 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 2 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 3 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 4 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 5 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 6 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 7 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 8 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 9 | 0 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 10 | 1 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 11 | 1 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 12 | 1 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 13 | 1 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 14 | 4 | 0 | 0 | 0 |
HOURLY | 17-02-2017 | 15 | 10 | 4 | 6 | 0 |
HOURLY | 17-02-2017 | 16 | 10 | 4 | 6 | 0 |
HOURLY | 17-02-2017 | 17 | 10 | 4 | 6 | 0 |
HOURLY | 17-02-2017 | 18 | 10 | 4 | 6 | 0 |
HOURLY | 17-02-2017 | 19 | 6 | 2 | 6 | 0 |
HOURLY | 17-02-2017 | 20 | 6 | 2 | 6 | 0 |
HOURLY | 17-02-2017 | 21 | 6 | 2 | 6 | 0 |
HOURLY | 17-02-2017 | 22 | 4 | 1 | 5 | 0 |
HOURLY | 17-02-2017 | 23 | 4 | 1 | 5 | 0 |
and :
Resolution | Date | Severity | Count |
DAILY | 17-02-2017 | CRITICAL | 10 |
DAILY | 17-02-2017 | MAJOR | 4 |
DAILY | 17-02-2017 | MINOR | 6 |
DAILY | 17-02-2017 | WARNING | 0 |
Share the sample data and the app ...let me check and get back to you
Dear Avinash,
Many thanks.
Please see the below data in excel and the require output also.
Requesting you to please help me in order to get the desired output.
Requirements ->Count of distictnt alarms per hour till it gets dropped.(Output table built accordingly)
check this ..
Hope this helps you
Dear Avinash,
I have checked the TCOM.qvw ,HERE THE COUNT as per hourly break up is completely different to excel output.
For example Hour 3 is shoing count(4) whereas there is no Count shpiung on hour 3 a sper excel output.
Same goes for different hours.
Can you please re-check the output of TCOM and suggest?
Its according to you data set check the input sheet you have given
If we calculate the hours , for AlarmID 1,2, 4 and 5 Critical we have 4 count that is showing in the output of our file
You show load the full data set and check
Dear Avinash,
Many thanks for your help on this so far.
If I we SELECT Hour =3 ,then count should be Zero because there was no AlarmID exists which was raised on or before 3 am .
The first alarmed that was raised as per Input Structure it is 10:30 AM (Alrmid=10).
So previuos to Hour 10 all count shoud be Zero .
Now next Hour=11 SAY ,HERE Alarmid=10 is still not dropeed so Count of Alarmid=1 for Hour =11 AS PER EXCEL OUTPUT.
sAME GOES FOR all hours calcualtions.
Please suggest.