topic Re: load the max date in the pivot table in QlikView

load the max date in the pivot table

Thu, 21 Jan 2016 07:20:32 GMT

Hi all member

i Used a pivot Table to get a max date for any task

i used task like demension and the max date in expression(its necessary to use it like this)

i have two script

this Tache is a concatination between other field, and join it with other table :

for the first script :

load

Distinct NameTache&'-'typeTask as Tache,

(MakeDate(1900+left(DateTask,3))+mid(DateTask,4)-1) as DateTask ;

SQL SELECT

NameTache,

typeTask,

DateTask // the max methode doesn't work here max(DateTask) as dateMax an error message apear that we cann't put the max methode

FROM "database" ;

-------------------------------------------------------------------

for the second script :

load

Distinct NameTacheee&'-'typeTaskeee as Tache,

SQL SELECT

NameTache,

typeTask,

FROM "database" ;

please i have tired to resolve this problem but i cant get it

- i used variable vMAxdate =max(DateTask) and i used it in expression in the my pivot table but doesn't work

- i tried to used it in script to load the max date like:

max(DateTask ) as DateTask_ also doesn't work

please help me

Re: load the max date in the pivot table

vikasmahajan — Thu, 21 Jan 2016 11:02:42 GMT

you need to create other table like this

MaxDate:

NoConcatenate

Load

date(Max(PostingDate),'MMMYY') as MaxDate

From [..\Qvds\Tracking.qvd] (qvd);

store this Maxdate in some variable & use this same or directly also you can use like =MaxDate

HTH

Vikas

Re: load the max date in the pivot table

Thu, 21 Jan 2016 12:05:19 GMT

Hi, thanks for your answer.

Could you please give me more explication because I didn't understand you well;

because I want only the max date for the concatenate field for the first table , these date situate in the second table(2ed script)

Thanks, Vikas

Note: Edited by Community Moderator for spelling and grammar.

Re: load the max date in the pivot table

vikasmahajan — Fri, 22 Jan 2016 04:52:13 GMT

create other table in load script using following syntax

MaxDate:

NoConcatenate

Load

date(Max(PostingDate),'MMMYY') as MaxDate

From [..\Qvds\Tracking.qvd] (qvd);

In variable overview create variable vMaxdate

assign value =Maxdate to variable like this in variable overview vMaxdate = Maxdate

now you can use vMaxdate variable which will hold max date value irrespective of any selection this value will be constant.

HTH

Vikas

Re: load the max date in the pivot table

tamilarasu — Fri, 22 Jan 2016 05:18:39 GMT

Try something like below,

Table1:
Load Distinct NameTache&'-'typeTask as Tache,
DateTask ;
SQL SELECT
NameTache,
typeTask,
DateTask  
FROM "database" ;

MaxDate:
Load Max(DateTask) as MaxDate Resident Table1;

Let vMaxDate = Peek('MaxDate',0,'MaxDate');
Drop Table MaxDate;
//____________________________
//Your second script

You can use vMaxDate variable in pivot table.

Re: load the max date in the pivot table

Sun, 24 Jan 2016 08:22:28 GMT

Hi thanks for your answer

but it doesn't work ,it desplay the same date for all Rows

realy ,i tired to resolve it

Re: load the max date in the pivot table

Tue, 26 Jan 2016 09:52:18 GMT

I think that is work

I stored the script into QVD file and import it

i used in expression of the pivot table tha aggr function aggr(max(date(DateTask) ),TAsk)

maybe that help other members

thanks All