Join in Qlik

[qlikviewaf]

Hi Guys,

easy question for you  .

Max(Year) in my script is not working. The goal is to have the current year if the DRKY=07.

Year is a field of my calendar.

How i can do this?

Thank you

[ankur_abhishek]

Could please share the sample to check it.

[vishsaggi]

Yes, Max() is an aggr function, you need Group By clause for this.

LOAD

DRKY,

IF(Match(trim(DRKY), '07'), Max(Year), Left(DRDL01,4)) AS YearUnitCost

Group By DRKY;

SQL Select *

FROM .....

WHERE DRRT = 'CM' AND DRSY = '40';

May be if you are looking for Current Year why dont your use like

Year(Today())

LOAD

DRKY,

IF(Match(trim(DRKY), '07'), Year(Today()), Left(DRDL01,4)) AS YearUnitCost;

SQL Select *

FROM .....

WHERE DRRT = 'CM' AND DRSY = '40';

[RSvebeck]

Add both fields in the group by:

LOAD

DRKY,

IF(Match(trim(DRKY), '07'), Max(Year), Left(DRDL01,4)) AS YearUnitCost

Group By DRKY,Left(DRDL01,4));

SQL Select *

FROM .....

WHERE DRRT = 'CM' AND DRSY = '40';

[vishsaggi]

Oh thanks, it got skipped. You are right.

[Anonymous]

Use year(today()) in place of max(Year).