Help on Nested IF

[#1]

Hi Team,

I have Two fields like [Emp Hiring Year] and normal [Calendar Year]

Now issue is if [Emp Hire Year]=Max(Year) then emp status considered as CY

if[Emp Hire Year]=Max(Year)-1 then PY

if[Emp Hire Year]=Max(Year)-2 then LY

and if emp status is CY or PY i should display value "In" or if emp status is LY or any other i have to display "Out" as a value in list box.

i have took a list box and in general instead of field i took expression.

can anyone help me with the expression.

[ToniKautto]

From my understanding you are trying to create a calculated dimension. Theoretically that means that you create a dimension that does not exist in your data model, which will cost you calculation time in run-time. A calculated dimension can also give you logical problems, if you can not predict how QlikView will relate the calculated dimension to your data model.

My general advise is that you should always try to avoid creating a calculated dimension if you can pre-calculate it in your script.

Would it be a feasible solution to pre-calculate the  CY/PY/LY and In/Out flags during reload and add them as fields in your data model?

[MarcoWedel]

Hi,

one solution could be:

hope this helps

regards

Marco

[maxgro]

another solution at the script level; script is a copy of Marco's script (thanks) with some (bold) change

tabCalendar:

LOAD RecNo()+1995 as [Calendar Year]

AutoGenerate 20;

tmp: LOAD max([Calendar Year]) as MaxYear Resident tabCalendar;

LET vMaxYear=Peek('MaxYear');

DROP Table tmp;

tabEmp:

LOAD

  *,

  Pick(WildMatch([Emp Hiring Year],$(vMaxYear),$(vMaxYear)-1,'*'),'In','In','Out') as [In/Out],

  Pick(Match([Emp Hiring Year],$(vMaxYear),$(vMaxYear)-1,$(vMaxYear)-2),'CY','PY','LY') as Status;

LOAD RecNo() as EmpID,

     Ceil(Rand()*20+1995) as [Emp Hiring Year]

AutoGenerate 300;