topic Re: Add next days data before 5 AM under previous day's day in QlikView

Add next days data before 5 AM under previous day's day

haneeshmarella — Sat, 16 Nov 2024 04:35:26 GMT

Hi everyone,

I have the below fields.

DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY') as MOD3_DATE,
INTERVAL(TIME#(NUM(MOD3_TIME,'000000'),'hhmmss'),'hh:mm:ss') as MOD3_TIME,
TIMESTAMP(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY') + INTERVAL(TIME#(NUM(MOD3_TIME,'000000'),'hhmmss'),'hh:mm:ss')) as MOD3_TIMESTAMP,
WEEK(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as MOD3_WEEK,
WEEKDAY(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as MOD3_WEEKDAY

I need to add the data under previous day's day if it is before 5 AM the next day. For example, if MOD3_WEEKDAY is Thursday and has data from 12 AM to 5 AM, it should show up under Wednesday on filtering.

Any help is highly appreciated. 🙂

Re: Add next days data before 5 AM under previous day's day

sunny_talwar — Thu, 31 Jan 2019 19:29:10 GMT

May be try this

If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY')) as MOD3_DATE,

WeekDay(
  If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
  Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
  Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY'))
) as MOD3_WEEKDAY

Re: Add next days data before 5 AM under previous day's day

haneeshmarella — Thu, 31 Jan 2019 19:36:12 GMT

Thanks a lot Sunny!