Adding New Column

[AnjuMR27]

I am working with 5 years of data Which I have concatenated and for the 5th year, a new column has been added. My task is to compare two columns — A and B  which is the new column — and create a new column based on that comparison.

The logic I am using to create the new column is as follows:

If(A = '1', '1',
If(A = '2', '2',
If(A = '3',
If(B = '01', '01',
If(B = '02', '02',

If(B = '03', '03',

If(B = '04', '04', '')))),
If(A = 'Not applicable',
If(B = '01', '01',
If(B = '02', '02',
If(B = '03', '03',
If(B = '04', '04', '')))),
Null())))) as NewColumn

When I apply this code to generate the new column, it is unexpectedly changing the "A" column. The logic is working as intended for the most part, but it seems that the calculation for the new column is affecting "A" in ways I didn't anticipate.  How to fix it and can anyone explain what I am doing wrong here

What I am getting is 

AcademicYear	A	B	Newcolumn
2022/23	Not applicable	01	1
2022/23	Not applicable	02	2
2022/23	Not applicable	03	03
2022/23	Not applicable	04	04
2022/23	01	Not applicable	1
2022/23	02	Not applicable	2
2022/23	03	01	1
2022/23	03	02	2
2022/23	03	03	03
2022/23	03	Not applicable
2018/19
2019/20			1
2019/20			2
2019/20			03
2020/21			1
2020/21			2
2020/21			03
2021/22			1
2021/22			2
2021/22			03

What I need is 

AcademicYear	A	B	New column
2018/19	1		1
2018/19	2		2
2018/19	3		3
2019/20	1		1
2019/20	2		2
2019/20	3		3
2020/21	1		1
2020/21	2		2
2020/21	3		3
2021/22	1		1
2021/22	2		2
2021/22	3		3
2022/23	Not applicable	01	01
2022/23	Not applicable	02	02
2022/23	Not applicable	03	03
2022/23	Not applicable	04	04
2022/23	1	Not applicable	1
2022/23	2	Not applicable	2
2022/23	3	Not applicable	3
2022/23	3	01	01
2022/23	3	02	02
2022/23	3	03	03

[Vegar]

When do you run into trouble? Can you show us an example on what you anticipated and what you got?

If(

[AnjuMR27]

i want something like this 

AcademicYear	A	B	New column
2018/19	1		1
2018/19	2		2
2018/19	3		3
2019/20	1		1
2019/20	2		2
2019/20	3		3
2020/21	1		1
2020/21	2		2
2020/21	3		3
2021/22	1		1
2021/22	2		2
2021/22	3		3
2022/23	Not applicable	01	01
2022/23	Not applicable	02	02
2022/23	Not applicable	03	03
2022/23	Not applicable	04	04
2022/23	1	Not applicable	1
2022/23	2	Not applicable	2
2022/23	3	Not applicable	3
2022/23	3	01	01
2022/23	3	02	02
2022/23	3	03	03

The main data set is big with millions of  entries. 5 year data is concatenated to a single table. only 5th year has both A and B. Rest has only A

what I am getting is 

AcademicYear	A	B	New Column
2022/23	Not applicable	01	1
2022/23	Not applicable	02	2
2022/23	Not applicable	03	03
2022/23	Not applicable	04	04
2022/23	01	Not applicable	1
2022/23	02	Not applicable	2
2022/23	03	01	1
2022/23	03	02	2
2022/23	03	03	03
2022/23	03	Not applicable
2018/19
2019/20			1
2019/20			2
2019/20			03
2020/21			1
2020/21			2
2020/21			03
2021/22			1
2021/22			2
2021/22			03

[marcus_sommer]

Creating a new column won't change other columns regardless if they are used to build the new one or not.

More likely is that you misinterpret the data from this specific load. If there are more loads/tables on which these data are concatenated/joined/mapped and/or there are associative tables within the data-model it may not be easy to look on the data in the right way.

This could be checked by disabling/commenting everything else unless this load or doing this load twice and the second time it's qualified, like:

qualify *; x: load * resident X; unqualify *;

Beside this I think I would query the year at first to apply the column-comparing only to the relevant subset and also reducing the number of the nested if-loops with match() for those cases in which the lookup and return value are equally respectively reverting the logic a bit to use them as default-values instead of the null().

[AnjuMR27]

Thank you I am not that much familiar with Qlikview. it is just 1 week.

LOAD 1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
A,
B,
15,
16,
17,
18,
counter, This is how my data will look like.- can you give me a simple explanation . Thank you in advance

[marcus_sommer]

Maybe something in this way:

t: load *,
   if(Year < MyYear, null(),
   if(match(A, 1, 2), A,
   if(match(A, 3, Not applicable'), if(match(B, '01', '02', '03', '04'), B, ''), null()))) as Y,
   recno() as RecNo, rowno() as RowNo
   from X;

qualify *; x: load * resident t; unqualify *;