Difference Between Maximum and Previous Date

[pandiarajan]

Hi Team,

     i would like to calculate date difference between Maximum and Previous maximum date by Customer

Input

Customer_ID	Customer_Name	Purchase_Date
1	Pandiarajan	2017-01-01
1	Pandiarajan	2017-01-15
1	Pandiarajan	2017-01-23
1	Pandiarajan	2017-02-10
2	Raja	2017-01-03
2	Raja	2017-01-12
2	Raja	2017-01-31

Output

Customer ID  Customer Name  Max_Date     Second_Max_Date    Difference

1                     Pandiarajan         2017-02-10   2017-02-23                   19

2                     Raja                      2017-01-31  2017-02-12                   19 

Thanks Lot

[sunny_talwar]

Try like this

Table:

LOAD * INLINE [

    Customer_ID, Customer_Name, Purchase_Date

    1, Pandiarajan, 2017-01-01

    1, Pandiarajan, 2017-01-15

    1, Pandiarajan, 2017-01-23

    1, Pandiarajan, 2017-02-10

    2, Raja, 2017-01-03

    2, Raja, 2017-01-12

    2, Raja, 2017-01-31

];

OutputTable:

LOAD Customer_ID,

Date(Max(Purchase_Date)) as Max_Date,

Date(Max(Purchase_Date, 2)) as Second_Max_Date,

Max(Purchase_Date) - Max(Purchase_Date, 2) as Difference

Resident Table

Group By Customer_ID;

[vishsaggi]

Try this:

Table1:

LOAD * INLINE [

Customer_ID, Customer_Name, Purchase_Date

1, Pandiarajan, 2017-01-01

1, Pandiarajan, 2017-01-15

1, Pandiarajan, 2017-01-23

1, Pandiarajan, 2017-02-10

2, Raja, 2017-01-03

2, Raja, 2017-01-12

2, Raja, 2017-01-31

];

NoConcatenate

Final:

LOAD Customer_ID,

     Customer_Name,

     Date(Max(Purchase_Date,1), 'YYYY-MM-DD') AS MaxPurchaseDate,

     Date(Max(Purchase_Date,2), 'YYYY-MM-DD') AS [2nd MaxPurchaseDate]

Resident Table1

Group By Customer_ID, Customer_Name;

Drop Table Table1;

Use straight table with all the dimensions and

expr: = MaxDatePurchase - [2nd MaxPurchaseDate]

[its_anandrjs]

In Backend script you required this or in the UI part i suggest do this in script part

Source:

LOAD Customer_ID, Customer_Name, Date(Date#(Purchase_Date,'YYYY-MM-DD'),'YYYY-MM-DD') AS Purchase_Date

Inline

[

Customer_ID, Customer_Name, Purchase_Date

1, Pandiarajan, 2017-01-01

1, Pandiarajan, 2017-01-15

1 ,Pandiarajan, 2017-01-23

1, Pandiarajan, 2017-02-10

2, Raja ,2017-01-03

2, Raja ,2017-01-12

2, Raja, 2017-01-31

];

FinalTable:

LOAD *,Max_Date - Second_Max_Date  as Diff;

LOAD Customer_ID, Customer_Name,  Date(Max(Purchase_Date)) AS Max_Date      ,Date(max(Purchase_Date,2)) AS Second_Max_Date

Resident Source

Group By Customer_ID, Customer_Name;

DROP Table Source;

OutPut Yor Required:-

[pandiarajan]

Thanks very much

[its_anandrjs]

Find the attached also

[pandiarajan]

thanks

[effinty2112]

Hi Pandiarajan,

This straight table seems to give you what you need:

Customer_ID	Customer_Name	Max(Purchase_Date)	Max(Purchase_Date,2)	Max(Purchase_Date)-Max(Purchase_Date,2)
1	Pandiarajan	2017-02-10	2017-01-23	18
2	Raja	2017-01-31	2017-01-12	19

cheers

Andrew