Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aj0031724
Partner - Creator
Partner - Creator

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

  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:

AlarmDateAlarm IDTechnologyVendorRNCNodeBCellNE LevelRaised TimeDropped TimeSeverity
17-02-20171483992308686UMTSZTER1S1 NODEB17-02-2017 15:1617-02-2017 18:16Critical
17-02-20171483992308685UMTSZTER2S1 NODEB17-02-2017 15:1617-02-2017 18:16Critical
17-02-20171483992308684UMTSZTER3S1 NODEB17-02-2017 15:16 Critical
17-02-20171483992308683UMTSZTER4S1EKOL0000JGPA3CELL17-02-2017 15:0817-02-2017 18:08Critical
17-02-20171483992308682UMTSZTER5S1EKOL0000JGPA2CELL17-02-2017 15:0817-02-2017 18:08Critical
17-02-20171483992308680UMTSZTER6S1EKOL0000JGPA1CELL17-02-2017 15:08 Critical
17-02-20171483992308679UMTSZTER7S1EKOL0000BOND3CELL17-02-2017 14:54 Critical
17-02-20171483992308678UMTSZTER8S1EKOL0000BOND2CELL17-02-2017 14:54 Critical
17-02-20171483992308673UMTSZTER9S1 NODEB17-02-2017 14:45 Critical
17-02-20171483992308672UMTSZTER10S1 NODEB17-02-2017 10:38 Critical
17-02-20171483992303238UMTSZTER11S1 NODEB17-02-2017 15:1317-02-2017 18:13Major
17-02-20171483992267154UMTSZTER12S1 NODEB17-02-2017 15:1417-02-2017 18:14Major
17-02-20171483992267153UMTSZTER13S1 NODEB17-02-2017 15:14 Minor
17-02-20171483992267147UMTSZTER14S1 NODEB17-02-2017 15:14 Major
17-02-20171483992267144UMTSZTER15S1 NODEB17-02-2017 15:14 Minor
17-02-20171483992267143UMTSZTER16S1 NODEB17-02-2017 15:13 Major
17-02-20171483992267142UMTSZTER17S1 NODEB17-02-2017 15:13 Minor
17-02-20171483992267134UMTSZTER18S1 NODEB17-02-2017 15:12 Minor
17-02-20171483992267133UMTSZTER19S1 NODEB17-02-2017 15:12 Minor
17-02-20171483992267132UMTSZTER20S1 NODEB17-02-2017 15:12 Minor
17-02-20171483992308686UMTSZTER21S1 NODEB17-02-2017 15:1617-02-2017 18:16Critical
17-02-20171483992308685UMTSZTER22S1 NODEB17-02-2017 15:1617-02-2017 18:16Critical
17-02-20171483992308684UMTSZTER23S1 NODEB17-02-2017 15:16 Critical
17-02-20171483992308683UMTSZTER24S1EKOL0000JGPA3CELL17-02-2017 15:0817-02-2017 18:08Critical
17-02-20171483992308682UMTSZTER25S1EKOL0000JGPA2CELL17-02-2017 15:0817-02-2017 18:08Critical
17-02-20171483992308680UMTSZTER26S1EKOL0000JGPA1CELL17-02-2017 15:0817-02-2017 21:08Critical
17-02-20171483992308679UMTSZTER27S1EKOL0000BOND3CELL17-02-2017 14:5417-02-2017 20:54Critical
17-02-20171483992308678UMTSZTER28S1EKOL0000BOND2CELL17-02-2017 14:54 Critical
17-02-20171483992308673UMTSZTER29S1 NODEB17-02-2017 14:45 Critical
17-02-20171483992308672UMTSZTER30S1 NODEB17-02-2017 10:38 Critical
17-02-20171483992303238UMTSZTER31S1 NODEB17-02-2017 15:1317-02-2017 18:13Major
17-02-20171483992267154UMTSZTER32S1 NODEB17-02-2017 15:1417-02-2017 18:14Major
17-02-20171483992267153UMTSZTER33S1 NODEB17-02-2017 15:1417-02-2017 21:14Minor
17-02-20171483992267147UMTSZTER34S1 NODEB17-02-2017 15:1417-02-2017 21:14Major
17-02-20171483992267144UMTSZTER35S1 NODEB17-02-2017 15:14 Minor
17-02-20171483992267143UMTSZTER36S1 NODEB17-02-2017 15:13 Major
17-02-20171483992267142UMTSZTER37S1 NODEB17-02-2017 15:13 Minor
17-02-20171483992267134UMTSZTER38S1 NODEB17-02-2017 15:12 Minor
17-02-20171483992267133UMTSZTER39S1 NODEB17-02-2017 15:12 Minor
17-02-20171483992267132UMTSZTER40S1 NODEB17-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 :

   

ResolutionDateHourCRITICALMAJORMINORWARNING
HOURLY17-02-201700000
HOURLY17-02-201710000
HOURLY17-02-201720000
HOURLY17-02-201730000
HOURLY17-02-201740000
HOURLY17-02-201750000
HOURLY17-02-201760000
HOURLY17-02-201770000
HOURLY17-02-201780000
HOURLY17-02-201790000
HOURLY17-02-2017101000
HOURLY17-02-2017111000
HOURLY17-02-2017121000
HOURLY17-02-2017131000
HOURLY17-02-2017144000
HOURLY17-02-20171510460
HOURLY17-02-20171610460
HOURLY17-02-20171710460
HOURLY17-02-20171810460
HOURLY17-02-2017196260
HOURLY17-02-2017206260
HOURLY17-02-2017216260
HOURLY17-02-2017224150
HOURLY17-02-2017234150

and :

   

ResolutionDateSeverityCount
DAILY17-02-2017CRITICAL10
DAILY17-02-2017MAJOR4
DAILY17-02-2017MINOR6
DAILY

17-02-2017

WARNING0
15 Replies
kuba_michalik
Partner - Specialist
Partner - Specialist

Use Hour() function.

E.g. when loading the input, add

Hour([Raised Time])) as Hour

to list of fields.

avinashelite

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

aj0031724
Partner - Creator
Partner - Creator
Author

Dear Avinash,

Yes There is Alarmdate column along with  RiasedTime and Dropped Time column as shown above in example.

aj0031724
Partner - Creator
Partner - Creator
Author

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?

avinashelite

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

avinashelite

You want to create the interval ? for each hour ?

then they check this

Creating Aging Buckets within QlikView

aj0031724
Partner - Creator
Partner - Creator
Author

Dear Avinash,

We need the below output from the INPUT STRCUTRE:

So output table requirement is :

   

ResolutionDateHourCRITICALMAJORMINORWARNING
HOURLY17-02-201700000
HOURLY17-02-201710000
HOURLY17-02-201720000
HOURLY17-02-201730000
HOURLY17-02-201740000
HOURLY17-02-201750000
HOURLY17-02-201760000
HOURLY17-02-201770000
HOURLY17-02-201780000
HOURLY17-02-201790000
HOURLY17-02-2017101000
HOURLY17-02-2017111000
HOURLY17-02-2017121000
HOURLY17-02-2017131000
HOURLY17-02-2017144000
HOURLY17-02-20171510460
HOURLY17-02-20171610460
HOURLY17-02-20171710460
HOURLY17-02-20171810460
HOURLY17-02-2017196260
HOURLY17-02-2017206260
HOURLY17-02-2017216260
HOURLY17-02-2017224150
HOURLY17-02-2017234150

and :

   

ResolutionDateSeverityCount
DAILY17-02-2017CRITICAL10
DAILY17-02-2017MAJOR4
DAILY17-02-2017MINOR6
DAILY

17-02-2017

WARNING0

  

  •   
  •         
avinashelite

Try like this in the script

interval( [Dropped Time]- [RiasedTime], 'hh') as Hour,

aj0031724
Partner - Creator
Partner - Creator
Author

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 ?