load the max date in the pivot table

[#1]

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

[vikasmahajan]

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

[#3]

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.

[vikasmahajan]

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

[tamilarasu]

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.

[#6]

Hi thanks for your answer

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

realy ,i tired to resolve it

[#7]

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