How to get distinct sum based on date.

[#1]

Hi

There is a requirement where Amount is same for one order number and received on same date but on different timing.

Ex.

Order No.          Date                         Amount          Net_amt

1                    1/1/2000 01:00:00             -10               5

1                    1/1/2000 01:00:01             -10              4

1                     1/1/2000 01:00:03            -10               6

In my requirement I have to consider Amount only one time and Sum(Net_amt).

So I used Sum(Distinct Amount). But because of change in the time all the records are getting displayed in the table and getting summed up to -30.

Afterwards I have gone through some blogs and used this

sum ( aggr( sum(Amount), Order_No))

But even with this the results are displayed 0.

Help me out to overcome the problem!..

[sunny_talwar]

Do you have a field which only contains information about date and excludes time? If not, then may be create it in the script like this:

LOAD [Order No.],

          Date as TimeStamp,

          Date(Floor(Date)) as Date,

          Amount

FROM...

and then may be this

Dimension:

Order No.

Date

Expression

Sum(Distinct Amount)

[Anonymous]

Sunny,

If you are using Date(Floor()) combination then "Distinct" really required?

I think when we use Date(Floor()), it will return single entry of minimum date.

And in above case, it will return 1 record

Order No.          Date                         Amount          Net_amt

1                    1/1/2000             -10               5

correct me if I am wrong?

[sunny_talwar]

To tell you the truth, I am not 100% sure of what the required output is, but ignoring Net_amt for now, I think the OP does want to see just one row. I can be wrong, but that was my interpretation.

[#5]

Thanks for your reply Sunny & Balraj.

The desired output should look like

Order No.          Date                         Amount          Net_amt

1                    1/1/2000                       -10                    15

I want Amount to print based on date excluding time. For one order number and date the Amount should print only one time though there are multiple entries but Net_amt should be summed up for all the rows.

Please help me out for this scenario

[Anonymous]

Then don't use Floor(), try like this?

ABC:

Load

OrderNo,

Date(Date#(Date,'DD/MMM/YYYY')) as Date,

Amount,

Net_amt

From YourTable;

NoConcatenate

Load

*

resident ABC

group by Date;

Drop table ABC;

[sunny_talwar]

I still think that Floor is what you need to use

LOAD [Order No.],

          Date as TimeStamp,

          Date(Floor(Date)) as Date,

          Amount

FROM...

and then this:

Dimension:

Order No.

Date

Expression

Sum(Distinct Amount)

Sum(Net_Amt)

[#8]

Balraj, My table structure looks like

Load

Order_Date,

Amount,

Net_amount from

Table 1

Select

Order_Date,

Amount,

Net_amount from

Table 1;

Join(Table1)

Load * from Table 2

Select * from Table 2

Join (Table 1)

Load * from Table 3

Select * from Table 3

Load

Order_Date_RTRN as Order_Date,

Amount_RTRN as Amount,

Net_amount_RTRN as Net_Amount from

Table 4;

Select

Order_Date_RTRN,

Amount_RTRN ,

Net_amount_RTRN from

Table 4;

Join (Table 4)

Load * from Table 5

Select * from Table 5

Join (Table 4)

Load * from Table 6

Select * from Table 6

Concatenate(Table1)

LOAD *

Resident Table4;

DROP Table4;

In this case how to implement the above logic.

[#9]

I tried this logic, But data is not matching/not proper.

[puttemans]

Hello,

Please have a look at the code below. I think it meets your requirements.

It sums the Net_amt per orderno & date, while it also gives you only once the Amount.

100:

LOAD * INLINE [

OrderNo,          Date                  ,       Amount     ,     Net_amt,

1         ,           1/1/2000 01:00:00    ,         -10      ,         5,

1          ,          1/1/2000 01:00:01     ,        -10       ,       4,

1           ,          1/1/2000 01:00:03     ,       -10        ,       6,

2, 1/1/2000 01:02:00, -10 , 5];

NoConcatenate

200:

LOAD *,

  subfield(Date,' ', 1) as Newdate,

  subfield(Date,' ', 1)&'|'&OrderNo as link

RESIDENT 100;

DROP TABLE 100;

Map_total:

MAPPING LOAD

  link,

  sum(Net_amt)

RESIDENT 200

GROUP BY link;

NoConcatenate

300:

LOAD OrderNo,

  Newdate,

  Amount,

  Applymap('Map_total', link, 'none') as total

RESIDENT 200;

DROP TABLE 200;