MAX date LOAD

[sculptorlv]

Hello! Please help with the correct formula.

I have, the following code and results:

LOAD

FA_Number,

FA_Movement_Number,

Date_Month,

MAX(FA_Movement_Posting_Date) AS FA_Movement_Posting_Date_Max

RESIDENT DOCUMENT_ROWS

GROUP BY

FA_Number,

FA_Movement_To_Type,

FA_Movement_Number,

Date_Month

But I need to keep only LAST date for each month period. Here I have two rows for month okt 2013.

What do I do wrong?

Thank you for your help!

;

[kaushiknsolanki]

Hi,

I see that you have added one more level of aggregation i.e FA_Movement_To_Type. remove that from group by and check again.

Regards,

Kaushik Solanki

[sculptorlv]

I just forget to remove it from this example (I have a fev more dimension in real case). Still, I remove it and checked, no changes.

LOAD

FA_Number,

// FA_Class,

//FA_Movement_To_Type,

FA_Movement_Number,

Date_Month,

MAX(FA_Movement_Posting_Date) AS FA_Movement_Posting_Date_Max

RESIDENT DOCUMENT_ROWS

GROUP BY

FA_Number,

// FA_Class,

// FA_Movement_To_Type,

FA_Movement_Number,

Date_Month

;

[jyothish8807 · Accepted solution]

Try this:

LOAD

FA_Number,

Date_Month,

MAX(FA_Movement_Posting_Date) AS FA_Movement_Posting_Date_Max

  RESIDENT DOCUMENT_ROWS

  GROUP BY

FA_Number,

Date_Month;

Br,

KC

[sculptorlv]

Yes, you were right. I missed the point, this dimmension has different values.

How can I choose only row with MAX FA_Movement_Number first and with MAX FA_Movement_Posting_Date secondary?

[jyothish8807]

May be something like this:

Try this:

A:

LOAD

FA_Number,

Date_Month,

MAX(FA_Movement_Number) AS FA_Movement_Number_Date_Max

  RESIDENT DOCUMENT_ROWS

  GROUP BY

FA_Number,

Date_Month;

join (A)

B:

LOAD

FA_Number,

Date_Month,

MAX(FA_Movement_Posting_Date) AS FA_Movement_Posting_Date_Max

  RESIDENT DOCUMENT_ROWS

  GROUP BY

FA_Number,

Date_Month;

C:

Load

FA_Number,

Date_Month,

if(FA_Movement_Number_Date_Max > FA_Movement_Posting_Date_Max,FA_Movement_Number_Date_Max,FA_Movement_Posting_Date_Max) as New field

resident A;

drop table A;

Br,

KC