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 |
Use Hour() function.
E.g. when loading the input, add
Hour([Raised Time])) as Hour
to list of fields.
Where do you have the time with your dates ? without which how you will decide the time taken to work on the tickets ? do you have any start time and close time columns in your data
Dear Avinash,
Yes There is Alarmdate column along with RiasedTime and Dropped Time column as shown above in example.
Dear Jakub,
We have two separate DATETIME (Riased time and Dropped Time).
And alarm can be active for more than 1day also , as shown above where DropeedTime is null.
Can you advise to achive above output?
You have all the necessary data rite , join the tables or link then with the common key and use the Hour column that's it ..is any thing else you trying to do ? show the desired output you want so that it will be helpful for us to understand requirement
You want to create the interval ? for each hour ?
then they check this
Dear Avinash,
We need the below output from the INPUT STRCUTRE:
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 |
Try like this in the script
interval( [Dropped Time]- [RiasedTime], 'hh') as Hour,
Dear Avinash,
After using the above interval the desiried outpout is not achived,
Here if you see DropedTime can be null if it it does not happen in same day .It can pass over to Next day with same information till it gets dropped.
Can you please help ?