STRAIGHT TABLE DATE CAN BE MAPPED WITH 0-23 hours and then do the logic based on HOUR filter.?

[aj0031724]

  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

[avinashelite]

Share the sample data and the app ...let me check and get back to you

[aj0031724]

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)

[avinashelite]

check this ..

Hope this helps you

[aj0031724]

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?

[avinashelite]

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

[aj0031724]

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.