topic Re: Date and Time maximum data in QlikView

Date and Time maximum data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Data:

Prod, Sales,Date

A, 10, 10 Jan 15 4pm

A, 10, 10 Jan 15 9pm

B, 10, 10 Jan 15 8pm

B, 10, 10 Jan 15 11pm

C, 10, 10 Jan 15 9pm

A, 10, 15 Jan 15 1pm

A, 10, 15 Jan 15 3pm

A, 10, 15 Jan 15 6pm

B, 10, 15 Jan 15 9pm

C, 10, 15 Jan 15 3pm

C, 10, 15 Jan 15 9pm

C, 10, 15 Jan 15 7pm

A, 10, 21 Feb 15 7pm

B, 10, 21 Feb 15 4pm

B, 10, 21 Feb 15 6pm

C, 10, 21 Feb 15 11am

C, 10, 21 Feb 15 4pm

C, 10, 21 Feb 15 9pm

];

Please show the output ,

specific product and specific date wise ,max order date

Output :

A : 10 JAN 15 9pm

A : 15 JAN 15 3pm

A : 21 Feb 15 2pm

B : 10 Jan 15 11pm

B : 15 Jan 15 9pm

B : 21 Feb 15 6pm

C :10 Jan 15 9pm

C : 15 Jan 15 9pm

C : 21 Feb 15 9pm

Thanks

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 08:25:26 GMT

What is the logic.. Can you explain please?

I mean the logic to get below line.

A : 10 JAN 15 9pm

A : 15 JAN 15 3pm

A : 21 Feb 15 2pm

Re: Date and Time maximum data

Anonymous — Fri, 23 Jun 2017 09:21:13 GMT

Max time with max date(Compare with max time).

Ex: One day sales two time means (1 jan 2015 5pm & 1 jan 2015 9pm)

the max time is (1 jan 2015 9pm).

I want these answer.

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 09:25:00 GMT

Use

Prod as dimension

Max(Date) as Expression

Re: Date and Time maximum data

Anonymous — Fri, 23 Jun 2017 09:34:27 GMT

Hello Manish,

In back end how to do?

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 09:38:13 GMT

You want only Prod and MaxDateTime.. or you want sales as well.

If you want Sales of Prod then what is the logic for sales

Re: Date and Time maximum data

Anonymous — Fri, 23 Jun 2017 09:42:07 GMT

No required for sales.

Just want to see whether in one day max time .

that's all

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 09:47:07 GMT

SET DateFormat='DD MMM YY';

SET TimestampFormat='DD MMM YY hhTT';

Data:

Load

Prod,

Sales,

Date,

Date(Floor(Date)) as OnlyDate

Inline

[

Prod, Sales,Date

A, 10, 10 Jan 15 4pm

A, 10, 10 Jan 15 9pm

B, 10, 10 Jan 15 8pm

B, 10, 10 Jan 15 11pm

C, 10, 10 Jan 15 9pm

A, 10, 15 Jan 15 1pm

A, 10, 15 Jan 15 3pm

A, 10, 15 Jan 15 6pm

B, 10, 15 Jan 15 9pm

C, 10, 15 Jan 15 3pm

C, 10, 15 Jan 15 9pm

C, 10, 15 Jan 15 7pm

A, 10, 21 Feb 15 7pm

B, 10, 21 Feb 15 4pm

B, 10, 21 Feb 15 6pm

C, 10, 21 Feb 15 11am

C, 10, 21 Feb 15 4pm

C, 10, 21 Feb 15 9pm

];

Left Join(Data)

Load Prod, OnlyDate, TimeStamp(Max(Date)) as MaxDate Resident Data

Group By Prod, OnlyDate;

Re: Date and Time maximum data

Anonymous — Fri, 23 Jun 2017 09:57:41 GMT

I got wrong answer and , my question is easy.

Just need :

Max time with particular day.

Please show the output ,

specific product and specific date wise ,max order date

Output :

A : 10 JAN 15 9pm

A : 15 JAN 15 3pm

A : 21 Feb 15 2pm

B : 10 Jan 15 11pm

B : 15 Jan 15 9pm

B : 21 Feb 15 6pm

C :10 Jan 15 9pm

C : 15 Jan 15 9pm

C : 21 Feb 15 9pm

Thanks,

And i wrote your answer it give wrong answer.

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 10:01:41 GMT

How below is possible?

We don't have 2pm for 21st Feb for Prod A

A : 21 Feb 15 2pm

Re: Date and Time maximum data

Anonymous — Fri, 23 Jun 2017 10:03:12 GMT

Ex:

1 jan 9pm

1 jan 5pm

max sales time is : 1 jan 9pm

That i expect

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 10:48:10 GMT

Where is 1st Jan in your sample data...??????

Re: Date and Time maximum data

Peter_Cammaert — Fri, 23 Jun 2017 10:49:08 GMT

Can you tell us where exactly Manish' solution goes wrong? As far as I can see, it works perfectly. Given the source data, the latest time per Prod per day is listed in each and every case.

Re: Date and Time maximum data

MK_QSL — Fri, 23 Jun 2017 11:55:07 GMT

aniket_2701‌what's wrong with my answer?

I had given the same one !!