topic Re: Sum maximum date in QlikView

Sum maximum date

Mon, 14 Oct 2013 08:08:40 GMT

Hi,

My data is like that

5-Oct-10	215
5-Oct-10	1,143
6-Oct-10	1,158
7-Oct-10	1,103
8-Oct-10	1,069
9-Oct-10	1,099
10-Oct-10	974
11-Oct-10	1,123
12-Oct-10	1,069
12-Oct-10	974
12-Oct-10	951
12-Oct-10	154
12-Oct-10	456
12-Oct-10	544
12-Oct-10	265

I want to calculate just maximum date like

10/5/2010	215
10/5/2010	1143
10/6/2010	1158
10/7/2010	1103
10/8/2010	1069
10/9/2010	1099
10/10/2010	974
10/11/2010	1123
10/12/2010	4413

Is it possible in qlikview with out using loading multiple this is dummy data we have more that 14 million records and it is very hard to reload this.

Re: Sum maximum date

vivientexier — Mon, 14 Oct 2013 08:15:39 GMT

LOAD

Date,

Max(Sales) as Sales

From ...

Group By Date

;

Re: Sum maximum date

CELAMBARASAN — Mon, 14 Oct 2013 08:27:41 GMT

I dont get the word "loading multiple". you could probably use Resident load once it is loaded.

Could you please explain if i missed anything.

Re: Sum maximum date

Mon, 14 Oct 2013 08:40:29 GMT

Adhimulam,

I dont want to load this multiple of time.

due to no of records we want to load just one time rather then Load first and get maximum date and load again to get our desired result.

Re: Sum maximum date

Mon, 14 Oct 2013 08:44:34 GMT

Texier,

If you know the function of group by it will sum all dates.

And we want to sum on highest value of dates not on any other date

Re: Sum maximum date

Mon, 14 Oct 2013 11:07:05 GMT

Try this

Load *,

Date,

sum(sales) as Sale

Resident Table A

Group by Date;

Re: Sum maximum date

Mon, 14 Oct 2013 11:25:01 GMT

This works:

Data:

LOAD Date,
Sale
FROM

(biff, embedded labels, table is [Sheet1$]);

NoConcatenate

A:
Load Date,
Sum(Sale) as Sale Resident Data
Group by Date;

Drop Table Data;

Re: Sum maximum date

DavidFoster1 — Mon, 14 Oct 2013 12:06:11 GMT

I would suggest looking at:

IF(DATE=MAX(DATE),SUM(SALES)