Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharthsoam
Partner - Creator II
Partner - Creator II

Can we use the max function in load scipt in Qliksense

Hi ,

I want to use max function at backend to find the maximum report date coming from multiple bases.

Is it possible?

11 Replies
Chanty4u
MVP
MVP

you can

table:
LOAD Project,
Issue,
[Issue ID],
[Issue Update ID],
[Issue Update Comment],
[Date of Update],
[Date of Update]&'_'&Project as _key
FROM
D:\QlikView\AXS\data\test.xlsx
(
ooxml, embedded labels, table is Feuil1);

Mytable:
Load Project as ID,
max(date([Date of Update],'DD/MM/YYYY')) as maxdate
Resident table
group by Project;

siddharthsoam
Partner - Creator II
Partner - Creator II
Author

Hi Chanty,

Thanks for the answer ...

I am trying to find a month before my maxdate.

Trying to use the following expression-

max(date(addmonths(Max(Date#(reportdate,'DD-MMM-YYYY')),-1),'DD-MMM-YYYY')) as maxdate,

But unable to ..

Appreciate your help in this.

Chanty4u
MVP
MVP

before max date means? 

can you share sample 3,4 lines of data through inline and what is your expected result?

siddharthsoam
Partner - Creator II
Partner - Creator II
Author

Yes,

So if my max date is '31-Jan-2018' my field maxdate should have the value '31-Dec-2017'

Chanty4u
MVP
MVP

you want monthend ?

=monthend(today()-1)

siddharthsoam
Partner - Creator II
Partner - Creator II
Author

Hi Chanty,

But I want to find the maximum date from the base , Today() will give me the present date.

OmarBenSalem

let's assume ur have this table:

loac a,b,c, date from source;

create ur monthdate field :

table:

loac a,b,c, date , Month(date)&' '&year(date) as MonthYear from source;


//now load this new MonthYear field in a resident table:


Months:

load distinct MonthYear resident table order by MonthYear ascend;


//now, let's see how many rows there is in this table:

vNbRows=NoOfRows('Months');

// and now let's peek the previous to last month (=the previous to last row)

vPrevMonth=peek('Months',$(vNbRows)-1,'MonthYear');

and there u have it

Hope this helps

avinashelite

Could you consider the same dates and the desired output your looking for ? so that will help us to understand better

siddharthsoam
Partner - Creator II
Partner - Creator II
Author

I have this reportdate column in my base

if("Month"='Oct' and "Fiscal Year"='2017','31-Oct-2017', if("Month"='Sep' and "Fiscal Year"='2017','30-Sep-2017',if("Month"='Mar' and "Fiscal Year"='2016','31-Mar-2017',if("Month"='Aug' and "Fiscal Year"='2017','31-Aug-2017', if("Month"='Nov' and "Fiscal Year"='2017','30-Nov-2017' ,if("Month"='Dec' and "Fiscal Year"='2017','31-Dec-2017',if("Month"='Jan' and "Fiscal Year"='2017','31-Jan-2018'))))))) as reportdate

The most recent reportdate i.e.- 31-Jan-2018

I want an expression which can return a month prior to the most recent date i.e. -31-Dec-2017