Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
;
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
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
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
;
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
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?
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