How can i exclude double records with an expression in a chart

[geogou1973]

Hello.

I'm facing a problem with a database which some times upload double records. I'm giving an example.

Date              Store     Hour     Sales

08/01/2015     101       10:00    20.000

08/01/2015     101       10:07    20.200

08/01/2015     102       10:01    15.000

08/01/2015     102       10:08    15.350

08/01/2015     103       10:02    13.100

08/01/2015     103       10:09    13.400

In a chart how can i  take only the last or the max Sales with summing the sales by Date and Hour and avoid the double record?

In the Hour a take only the first digits e.g  10

Thank you in advance.

[alexandros17]

You have in the same day more than one store, if you avoid double records you loose that information, however you can:

Select Date, sum(Sales) from mytable Group by Date

Let me know

[morganaaron]

Given your data format, If you create a straight table for example and use Date, Store and Hour(Hour) as your dimensions, then max(Sales) as an expression would give you the max sales for each store on each date for each hour (so would only take '10' as the hour entry from data provided).

[SunilChauhan]

Load *

time( Hour  , 'hh' ) as HourNew ;

Load * inline [

Date     ,         Store  ,   Hour  ,   Sales

08/01/2015  ,   101    ,   10:00 ,   20.000

08/01/2015   ,  101  ,     10:07  ,  20.200

08/01/2015 ,    102   ,    10:01,    15.000

08/01/2015,     102     ,  10:08   , 15.350

08/01/2015   ,  103 ,      10:02  ,  13.100

08/01/2015   ,  103,       10:09  ,  13.400

];

now use Hournew inpace of hour

[jagan]

Hi,

Try like this

Data:

LOAD

*

FROM DataSource;

Inner Join

LOAD

Date,

Store,

MaxString(Hour) AS Hour

RESIDENT Data;

Hope this helps you.

Regards,

Jagan.

[#6]

hope attached files helps you

[geogou1973]

Thank you for your help, but if i want to sum the data in date what can i do ?