topic Re: Join in Qlik in QlikView

Join in Qlik

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

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 — Fri, 23 Jun 2017 15:24:20 GMT

Could please share the sample to check it.

vishsaggi — Fri, 23 Jun 2017 15:29:48 GMT

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 — Fri, 23 Jun 2017 16:19:13 GMT

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 — Fri, 23 Jun 2017 16:23:28 GMT

Oh thanks, it got skipped. You are right.

Anonymous — Fri, 23 Jun 2017 16:45:13 GMT

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