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: 
Not applicable

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

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.

2 Replies
rubenmarin

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.

Not applicable
Author

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