How to get latest month from the data set

[Anonymous]

I have the data like in the month column

Jan

Feb

March

April

May......

Now I want latest month dynamically.

My Output should be Month= "May"

Thank you

Sujeeth

[YoussefBelloum]

Hi,

here is one way to do it:

you should have your MonthNames system variable like this:

Set MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

max_month:

LOAD 1 as flag, Date#(month,'MMM') as month, num(month(Date#(month,'MMM'))) as monthnum Inline [

month

Jan

Feb

Mar

Apr

May

];

join

LOAD max(monthnum) as max_month

Resident max_month

Group by flag;

final:

NoConcatenate

LOAD * Resident max_month

Where monthnum=max_month;

DROP Table max_month;

PFA

[eduardo_dimperio]

LOAD

MONTH AS LAST MONTH

FROM YOURQVD

WHERE MONTH=MONTH(TODAY)

[YoussefBelloum]

your solution works only if you have the actual month in your dataset

[eduardo_dimperio]

yeap

[Anonymous]

Hi,

I did it as below:

LOAD * INLINE [
MonthID, Month
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];

Use these formulas:

=Max(Month) or

Month= Month(Today)

[vkish16161]

Sorting and using peek should be better I guess?

[rubenmarin]

Hi Sujeeth, you can try with:

=Month(Max(Date#(MonthField,'MMM')))

[Anonymous]

Hi,

As of now in my excel sheet data contains only March and April data. My requirement is when ever the data loaded to my excel sheet it should take the latest loaded month dynamically.

Could you please help me on this.

Thank you

Sujeeth

[rubenmarin]

You can apply something very close to Youssef answer to keep the highest month of data:

Set MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Data:

LOAD Month(Date#(monthField,'MMM') as month,

  num(month(Date#(monthField,'MMM'))) as monthnum,

OtherFields

FROM [ExcelFile];

// Inner Join to keep only the max month

Inner Join (Data)

LOAD max(monthnum) as monthnum

Resident Data;