If Statement to get right value in Script

[qlikwiz123]

My table is as below.

ID	ID Grade	Manager Grade	Manager ID	Job Title	Contact Manager ID (OUTPUT)
2	8	8	2	Intern
2	8	7	5	Executive	5
2	8	6	67	Executive Director
2	8	5	55	Director
2	8	4	8	Director
2	8	3	97	Director
2	8	2	23	Director
2	8	1	11	Director

I need to write an IF statement in the script where I need to create 'Contact Manager ID' Column.

Condition:

If  (ID Grade - Manager Grade) =1 and Job Title <> Intern, then the Manager ID is Contact Manager ID.

If  (ID Grade - Manager Grade)=1 and Job Title = Intern, then it should look at (ID Grade - Manager Grade)=2 and get that Manager ID as Contact Manager ID. 

How do I create Contact Manager ID column in the script using this logic?

[Or]

The latter half of your condition doesn't seem to match with the data provided (the only intern has ID Grade - Manager Grade = 0), so it's hard to understand what exactly you're trying to achieve with the second part of your condition. An example with matching data would help, hopefully.

The first part is just a basic if([ID Grade] - [Manager Grade])=1 and [Job Title] <> 'Intern',Manager ID) as [Contact Manager ID], but you probably already knew that. You might want to create [ID Grade] - [Manager Grade] as [Grade Gap] if you're going to be using this a lot.

[qlikwiz123]

Hi,

Here is an example.

ID	ID Grade	Manager Grade	Manager ID	Job Title	Contact Manager ID (OUTPUT)
2	8	7	5	Intern
2	8	6	67	Executive Director	67
2	8	5	55	Director
2	8	4	8	Director
2	8	3	97	Director
2	8	2	23	Director
2	8	1	11	Director

The general condition is if(ID Grade-Manager ID)=1 and Job Title<>'Intern', then Manager ID becomes Contact Manager ID.

In the above example, since (ID Grade - Manager ID)=1 has Job Title 'Intern', it should look at (ID Grade - Manager ID)=2 , which in this case gives Manager ID 67

[Or · Accepted solution]

And there is only ever going to be one record with that exact gap? There won't be, say, a record with ID Grade 16 and Manager Grade 14? Or records with 8 and 6 but with ID=3?

If this example fits with the actual data structure, you could ignore the question of what the grade gap is entirely and just peek down to the next record if the current record matches Gap = 1 and Job Title = Intern. This would be easier if you sort them in reverse order, because finding the previous record is easier than finding the next record, so for the sake of this I'll assume you can order them in reverse. In that case, I think you can then use:

If([ID Grade]-[Manager Grade] = 1 AND [Job Title] = 'Intern',Previous([Manager ID])

Note that this will only work if the data can be sorted in reverse order, and only if the next row down is guaranteed to have a gap of 2. You could probably add a check that the gap is actually two by using Previous([ID Grade]) and Previous([Manager Grade]) as well.

[qlikwiz123]

Hi,

True. The difference between ID Grade and Manager Grade will be unique and will never repeat at multiple levels. How do i sort it in reverse order in the script?

Attaching a sample QVW

[Or]

Either sort in the underlying SQL query if there is one, or in the underlying load, using ORDER BY. If need by you can use a second preceding load to read a first, sorted load.