topic Settting month order in scripting in QlikView

Settting month order in scripting

Anonymous — Thu, 23 Jul 2015 06:37:22 GMT

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

Re: Settting month order in scripting

Kushal_Chawda — Thu, 23 Jul 2015 06:41:25 GMT

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

Re: Settting month order in scripting

qlikmsg4u — Thu, 23 Jul 2015 06:43:02 GMT

Trying using Max(Month)

Re: Settting month order in scripting

sushil353 — Thu, 23 Jul 2015 06:48:30 GMT

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

Re: Settting month order in scripting

jagan — Thu, 23 Jul 2015 06:52:57 GMT

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.

Re: Settting month order in scripting

Anonymous — Thu, 23 Jul 2015 07:04:25 GMT

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.

Re: Settting month order in scripting

jagan — Thu, 23 Jul 2015 07:12:10 GMT

Hi,

Try like this

LOAD

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

FROM DAtaSource;

Now you can use Max(Month)

Regards,

Jagan.

Re: Settting month order in scripting

Anonymous — Thu, 23 Jul 2015 07:21:14 GMT

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

Kushal_Chawda — Thu, 23 Jul 2015 07:24:47 GMT

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');

Re: Settting month order in scripting

qlikviewwizard — Thu, 23 Jul 2015 07:57:31 GMT

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');

Re: Settting month order in scripting

jagan — Thu, 23 Jul 2015 08:27:28 GMT

Hi,

If you want create it in script then try below

TableName:

LOAD

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

FROM DAtaSource;

MaxMonth:

LOAD

Max(Month) AS MaxMonth

RESIDENT TableName;

LET vMaxMonth = Peek('MaxMonth',0,'MaxMonth');

DROP TABLE MaxMonth;

If you are this variable in script itself then this more useful, otherwise this is not best approach. Create it in Front end itself

Settings -> Document Properties -> Variables.

Hope this helps you.

Regards,

Jagan.

Re: Settting month order in scripting

Thu, 23 Jul 2015 09:00:30 GMT

As we have in our Main tab in script editor, all the months listed in a variable MonthNames, we can associate a number to each of such a month and then find the max value of it.

You can try like:

vMyVar= chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39);

TempMonth:
load Month as MonthName,Match(Month,$(vMyVar))as MonthNumber ;
load * inline [
Month
'Jul'
'Aug'
'Dec'
'Feb'
'Mar'
];

Now you can find the max value from here.