topic Adding New Column in Data Quality

Adding New Column

AnjuMR27 — Wed, 13 Nov 2024 10:30:33 GMT

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

Re: Adding New Column

Vegar — Wed, 13 Nov 2024 05:40:19 GMT

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

If(

Re: Adding New Column

AnjuMR27 — Wed, 13 Nov 2024 09:10:55 GMT

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

Re: Adding New Column

marcus_sommer — Wed, 13 Nov 2024 13:57:12 GMT

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().

Re: Adding New Column

AnjuMR27 — Wed, 13 Nov 2024 15:10:32 GMT

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

Re: Adding New Column

marcus_sommer — Sat, 16 Nov 2024 12:10:30 GMT

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 *;