topic Re: I have a problem with left join and setting date detween two dates: this is what i write: in Qlik Learning Discussions

I have a problem with left join and setting date detween two dates: this is what i write:

Fri, 28 Nov 2014 08:00:57 GMT

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.

Re: I have a problem with left join and setting date detween two dates: this is what i write:

rubenmarin — Fri, 28 Nov 2014 08:28:52 GMT

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.

Re: I have a problem with left join and setting date detween two dates: this is what i write:

Fri, 28 Nov 2014 08:46:30 GMT

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