Aggregation with Floor and Auto populate with previous record

[#1]

Hi Guys,

I am  an SSIS developer learning the ropes in qlikview.

Currently I have a project that requires some aggregation function with auto populate based on the previous record.

My raw data is something like this.

TIMESTAMP                               ID      DEPT                   

1/1/2016  1:05:00 AM                   1          HR                             

1/1/2016  1:15:00 AM                   1          HR

1/1/2016  1:25:00 AM                   1          HR

1/1/2016  4:05:00 AM                   1          MARKETING

1/1/2016  4:15:00 AM                   1          MARKETING

1/1/2016  5:05:00 AM                   1          MARKETING

The output required is to get the nearest time in the hour for each activity and fill in the missing hour with previous value

Desired output:

Date        HR             ID     DEPT

1/1/2016   01:00 am    1       HR

1/1/2016   02:00 am    1       HR

1/1/2016   03:00 am    1       HR

1/1/2016   04:00 am    1       HR

1/1/2016   05:00 am    1       MARKETING

1/1/2016   06:00 am    1       MARKETING

For the first requirement:

As you can see there are 3 records that has 1:00 am value, the objective is to get the aggregation of floor or nearest to the time with value.

For the second requirement:

As you can see here, the 2 am and 3 am is missing in the original data so there is a need to populate and at the same time need to fill the department base on the previous value.

Can you please guide me on how this can be done in Qlikview.

Thanks in advance,

with best regards,

MJ

[sunny_talwar]

To get the date, you can do this:

Date(Floor(TimeStamp)) as Date,

Time(Floor(Frac(TimeStamp), 1/24), 'hh:mm TT') as HR

[sunny_talwar]

Why is 4 am having HR and not MARKETING in the above desired result?

[Kushal_Chawda]

Your output is quite confusing,

for 1:05:00 AM  , output is 1:00:00 AM    but for 5:05:00 AM  , output is 6:00:00 AM

[Anonymous]

Hi MJ,

This code calculates previous and Accumulate hours by DEPT, if you want it for ID, Change DEPT by ID, else if you want it for all, delete IF(DEPT<>Previous(DEPT) condition.

Data:

//This load calculates Accumulate Hours

LOAD *,

    Time(IF(Previous(DEPT)<>DEPT,Hour_Prev,Hour_Prev+Peek('Hour_Accum',-1)))            as Hour_Accum;

//This load calculate first hours

LOAD *,

    IF(Previous(DEPT)<>DEPT,Hour,Peek('Hour_Prev',-1))                                    as Hour_Prev;

LOAD *,

    Date(Floor(TimeStamp#(TimeStamp,'D/M/YYYY h:mm:ss TT')))                             as Date,

    Time(TimeStamp#(TimeStamp,'D/M/YYYY h:mm:ss TT'), 'hh:mm TT')                         as Hour;

LOAD * INLINE [

    TimeStamp,                 ID,     DEPT

    1/1/2016 1:05:00 AM,     1,         HR

    1/1/2016 1:15:00 AM,     1,         HR

    1/1/2016 1:25:00 AM,     1,         HR

    1/1/2016 4:05:00 AM,     1,         MARKETING

    1/1/2016 4:15:00 AM,     1,         MARKETING

    1/1/2016 5:05:00 AM,     1,         MARKETING

];

Regards!

[#6]

Hi Sunny, Yes you are correct, sorry for the typo, but how can i have the end result? as mentioned

thanks

[#7]

Hi Kushal,

The reason is there are many records occur in 1:00 AM.

And I just want to get the nearest one that occur.

additional ex.

We are trying to monitor how long thus the cleaner stay in one department

Time  Department

1:05    HR

1;25    PANTRY

1:45    HR

As you can see the nearest time is 1:05 am. So in 1 am, the cleaner on duty is in HR room.

Hope this clears.

[sunny_talwar]

Try this:

Table:

LOAD DISTINCT Date(Floor(TIMESTAMP)) as Date,

  Time(Floor(Frac(TIMESTAMP), 1/24), 'hh:mm TT') as HR,

  ID,

  DEPT

INLINE [

    TIMESTAMP,                              ID,      DEPT

    1/1/2016  1:05:00 AM,                  1,          HR                           

    1/1/2016  1:15:00 AM,                  1,          HR

    1/1/2016  1:25:00 AM,                  1,          HR

    1/1/2016  4:05:00 AM,                  1,          MARKETING

    1/1/2016  4:15:00 AM,                  1,          MARKETING

    1/1/2016  5:05:00 AM,                  1,          MARKETING

];

FinalTable:

NoConcatenate

LOAD Date,

  Time(HR + ((IterNo() - 1)/24), 'hh:mm TT') as HR,

  ID,

  DEPT

While HR + ((IterNo() - 1)/24) < END_HR;

LOAD *,

  Time(If(ID = Previous(ID) and Date = Previous(Date), Previous(HR), HR + 2/24), 'hh:mm TT') as END_HR

Resident Table

Order By ID, Date, HR desc;

DROP Table Table;

[Anonymous]

HI MJ,

Have yoo seen my response?

You can do it with it.

Regards!

[#10]

Hi Sunny,

Thanks for this!

You answered what I exactly needed.

with best regards,

MJ