Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
matable:
LOAD
CNI,
[Nom Prénom],
[Maternité (Évènements)],
Date,
Min(if([Maternité (Évènements)]=1,date(Date))) as mindate,
Max(if([Maternité (Évènements)]=1,date(Date))) as maxdate
Resident Année2013
Group by CNI,[Nom Prénom],[Maternité (Évènements)], Date;
Left Join(matable)
LOAD
CNI,
[Nom Prénom],
[Maternité (Évènements)],
Date,
if(IsNull(mindate),2,if(Date>maxdate and Date<=date(mindate+365),1,0))as valmaternité
Resident matable;
the result that iwant to have is : if i don't have a min date then give me 2 , if i have a mindate then if date between mindate and mindate+one year then 1 else 0.
Hi arwa, in the first load of matable, if you are grouping by Date, you will retrieve one record per Date, so each row with [Maternité (Évènements)]=1 will have the same value for Date, mindate and maxdate
Also in the second table the Date>maxdate will return always false:
if(IsNull(mindate),2,if(Date>maxdate and Date<=date(mindate+365),1,0))
If mindate is not null, there will be a maxdate and it can't be lower than Date.
in the first load of matable,i am retrieving all the dates between mindate and maxdate. and if i omit date from field and from groupby ,i have one day for each person who have 1 for [Maternité (Évènements)]. but the if of the left join is not used.
if mindate is not null, then for any date between mindate and mindate+oneyear it takes 1