Solved: Aggregation with Floor and Auto populate with prev... - Page 2 - Qlik Community

Report Inappropriate Content · ‎2016-06-17

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

Report Inappropriate Content · ‎2016-06-19

Hi Manuel,

Thanks for this.

Your script is helpful but Sunny T got the exact answer I needed.

Really appreciate to you all.

I learned a lot from the short span of time.

with best regards,

MJ

Anonymous · ‎2016-06-20

Hi MJ,

Don't worry, I like use previous and peek to do things like this, because are more dynamic.

Regards!!

Report Inappropriate Content · ‎2016-06-20

Hi Sunny,

What if I need to fill in the whole time, 0-24 hrs.

Is that possible?

Thanks in advance,

with best reagrds,

MJ

sunny_talwar · ‎2016-06-20

May be 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) < 1;

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;

Report Inappropriate Content · ‎2016-06-22

Hi Sunny,

Thanks for this.

However I think there was an issue in overlapping value if the Dept occurs in same date with different time.

EX.

TIMESTAMP, ID, DEPT

3/30/2016 3:38:52 PM, 1, HR

3/30/2016 6:20:17 PM, 1, MARKETING

3/31/2016 2:30:09 AM, 2, HR

3/31/2016 3:55:04 AM, 1, HR

The desired output should be

TIMESTAMP, ID, DEPT

3/30/2016 3:00 PM, 1, HR

3/30/2016 4:00 PM, 1, HR

3/30/2016 5:00 PM, 1, HR

3/30/2016 6:00 PM, 1, MARKETING

3/30/2016 7:00 PM, 1, MARKETING

3/30/2016 8:00 PM, 1, MARKETING

3/30/2016 9:00 PM, 1, MARKETING

3/30/2016 10:00 PM, 1, MARKETING

3/30/2016 11:00 PM, 1, MARKETING

3/31/2016 12:00 AM, 1, MARKETING

3/31/2016 1:00 AM, 1, MARKETING

3/31/2016 2:00 AM, 1, MARKETING

3/31/2016 3:00 AM, 1, MARKETING

3/31/2016 4:00 AM, 1, HR

3/31/2016 5:00 AM, 1, HR

---------------------------------

3/31/2016 2:00 AM, 2, HR

3/31/2016 3:00 AM, 2, HR

3/31/2016 4:00 AM, 2, HR

3/31/2016 5:00 AM, 2, HR

3/31/2016 6:00 AM, 2, HR

etc

Once again thanks in advance. In solving my issue

with best reagrds,

MJ

sunny_talwar · ‎2016-06-22

I am not sure if this output looks good or not?

Script:

Table:

LOAD DISTINCT

RowNo() as Key,

TimeStamp(Floor(TIMESTAMP, 1/24)) as TIMESTAMP,

Date(Floor(TIMESTAMP)) as Date,

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

ID,

DEPT

INLINE [

TIMESTAMP, ID, DEPT

3/30/2016 3:38:52 PM, 1, HR

3/30/2016 6:20:17 PM, 1, MARKETING

3/31/2016 2:30:09 AM, 2, HR

3/31/2016 3:55:04 AM, 1, HR

];

FinalTable:

NoConcatenate

LOAD Date,

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

ID,

DEPT

While TIMESTAMP + ((IterNo() - 1)/24) < END_TIMESTAMP;

LOAD *,

TimeStamp(If(ID = Previous(ID), Previous(TIMESTAMP), TIMESTAMP + 2/24)) as END_TIMESTAMP

Resident Table

Order By ID, TIMESTAMP desc;

DROP Table Table;

Report Inappropriate Content · ‎2016-06-23

Hi Sunny,

I made some review.

And trying to debug because I saw a minor issue.

The date for 12 AM - 3 AM should be 3/31/2016 not 3/30/2016.

But I am trying to check how to resolved it but nevertheless thanks for the fantastic contribution.

I am trying to solve it also in my end.

Thanks.

with best regards,

MJ

sunny_talwar · ‎2016-06-23

Use of date was a little misleading there. I have fixed the issue now:

Table:

LOAD DISTINCT

RowNo() as Key,

TimeStamp(Floor(TIMESTAMP, 1/24)) as TIMESTAMP,

ID,

DEPT

INLINE [

TIMESTAMP, ID, DEPT

3/30/2016 3:38:52 PM, 1, HR

3/30/2016 6:20:17 PM, 1, MARKETING

3/31/2016 2:30:09 AM, 2, HR

3/31/2016 3:55:04 AM, 1, HR

];

FinalTable:

NoConcatenate

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

Date(Floor(TIMESTAMP + ((IterNo() - 1)/24))) as Date,

ID,

DEPT

While TIMESTAMP + ((IterNo() - 1)/24) < END_TIMESTAMP;

LOAD *,

TimeStamp(If(ID = Previous(ID), Previous(TIMESTAMP), TIMESTAMP + 2/24)) as END_TIMESTAMP

Resident Table

Order By ID, TIMESTAMP desc;

DROP Table Table;

Report Inappropriate Content · ‎2016-06-23

Hi Sunny,

In this few exchange, I learned a lot from you!

Kudos to you!

You Nailed it perfectly!

with best regards,

MJ

Aggregation with Floor and Auto populate with previous record