How to copy data from column A to column D if the column B or C data match

[xupmaupl]

Hello everyone, 

Got one below table which shows the match and group details:

Group 1	Match ID	Match Time	Group 2
A	1
B	2
C	3	07/07/2019
D	1
E	2
F		07/07/2019

How can I fill up the Group 2 by matching Match ID or Match Time using QlikView? If a Group A has no Match ID, use Match Time to find the Group 2. Below is the expected result:

Group A	Match ID	Match Time	Group B
A	1		D
B	2		E
C	3	07/07/2019	F
D	1		A
E	2		B
F		07/07/2019	C

Kind Regards,

Riva

[chrismarlow]

Hi,

Try something like the below. Note this only ends up stripping out duplicates introduced on the join as they drop out quite nicely.

Cheers,

Chris.

data:
LOAD * INLINE [
Group 1,Match ID,Match Time
A,1,
B,2,
C,3,07/07/2019
D,1,
E,2,
F,,07/07/2019
];

left join (data)
LOAD  distinct
	[Group 1] AS [Group 2_1],
	[Match ID]
resident data
where [Match ID]<>'';

left join (data)
LOAD distinct
	[Group 1] AS [Group 2_2],
	[Match Time]
resident data
where [Match Time]<>'';


data_final:
NoConcatenate
Load
	[Group 1],
	[Match ID],
	[Match Time],
	If(isnull([Group 2_1]),[Group 2_2],[Group 2_1]) AS [Group 2]
resident data
where ([Group 1]<>[Group 2_1]) and ([Group 1]<>[Group 2_2]);

drop table data;