topic Re: MAX date LOAD in QlikView

MAX date LOAD

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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!

;

Re: MAX date LOAD

kaushiknsolanki — Thu, 21 Jun 2018 08:36:48 GMT

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

Re: MAX date LOAD

sculptorlv — Thu, 21 Jun 2018 08:41:47 GMT

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

;

Re: MAX date LOAD

jyothish8807 — Thu, 21 Jun 2018 08:43:16 GMT

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,

Re: MAX date LOAD

sculptorlv — Thu, 21 Jun 2018 08:46:41 GMT

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?

Re: MAX date LOAD

jyothish8807 — Thu, 21 Jun 2018 09:02:51 GMT

May be something like this:

Try this:

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)

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;

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,