Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

shashank20
New Contributor III

Settting month order in scripting

Hi Genuises,

I have the months coming after loading the script are like below

Jun

Mar

Apr

May

I have to find the latest month and store in the variable in this case its Jun

I can use peek function like this trim(Peek('Month')); but the problem is the month order is not fixed,may be next month it will come like below

Jun

Aug

Apr

May

Mar

In this case I want Aug to store in variable.

Please help me

Thanks,

Shashank

Tags (1)
1 Solution

Accepted Solutions

Re: Settting month order in scripting

Try like this

Data:

Load *,

Date(Date#(Month, 'MMM'), 'MMM') AS Month

FROM table


MaxMonth:

load

max(Month) as MaxMonth

resident Data;


let vMaxMonth = peek('MaxMonth',0,'MaxMonth');

11 Replies

Re: Settting month order in scripting

If in script you have created the Month using Month(Date) as Month  , then you can directly write max(Month) in variable

qlikmsg4u
Valued Contributor

Re: Settting month order in scripting

Trying using Max(Month)

sushil353
Honored Contributor II

Re: Settting month order in scripting

Hi,

Try this:


LOAD Month(Date#(Month,'MMM')) as Month Inline
[
Month
Jan
Feb
Apr
Mar
Jun
Aug
]
;

then in QlikView if you use MaxString(Month) you will Get AUG

HTH

Sushil

MVP
MVP

Re: Settting month order in scripting

Hi,

How you are deriving month field in the script?  If you are deriving by using Month() then you can directly use

Max(MonthFieldName)

Regards,

Jagan.

shashank20
New Contributor III

Re: Settting month order in scripting

Month is coming from the text file and I am not converting it in any month format using QlikView function.I did that too but it is not working.

MVP
MVP

Re: Settting month order in scripting

Hi,

Try like this

LOAD

Date(Date#(Month, 'MMM'), 'MMM') AS Month

'

'

'

FROM DAtaSource;

Now you can use Max(Month)

Regards,

Jagan.

shashank20
New Contributor III

Re: Settting month order in scripting

I have to create the variable in the script itself and when I am using Max there it is not returning any value

Re: Settting month order in scripting

Try like this

Data:

Load *,

Date(Date#(Month, 'MMM'), 'MMM') AS Month

FROM table


MaxMonth:

load

max(Month) as MaxMonth

resident Data;


let vMaxMonth = peek('MaxMonth',0,'MaxMonth');

Arjunarao
Honored Contributor II

Re: Settting month order in scripting

Hi

Please use the below script:

EMP:

LOAD EMPNO,

    ENAME,

    JOB,

    date(DATE) as DATE,

    num(Month(date(DATE))) as Month_Num,

    SAL,

    DEPTNO,

    COMM

FROM

EMP.xlsx

(ooxml, embedded labels, table is Sheet1);

Max_Month_Num:

Load max(Month_Num) as Max_Month

Resident EMP;

Let vMaxMonthNum= peek('Max_Month',0,'Max_Month');


Capture.JPG

Community Browser