Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
LOAD
MONTH AS LAST MONTH
FROM YOURQVD
WHERE MONTH=MONTH(TODAY)
your solution works only if you have the actual month in your dataset
yeap
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)
Sorting and using peek should be better I guess?
Hi Sujeeth, you can try with:
=Month(Max(Date#(MonthField,'MMM')))
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
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;