topic Re: Help on Nested IF in QlikView

Help on Nested IF

Sat, 06 Jun 2015 03:59:54 GMT

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.

Re: Help on Nested IF

ToniKautto — Sat, 06 Jun 2015 04:14:47 GMT

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?

Re: Help on Nested IF

MarcoWedel — Sat, 06 Jun 2015 11:10:05 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Help on Nested IF

maxgro — Sat, 06 Jun 2015 17:21:08 GMT

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;