Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
xupmaupl
Contributor
Contributor

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

Hello everyone, 

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

Group 1Match IDMatch TimeGroup 2
A1  
B2  
C307/07/2019 
D1  
E2  
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 AMatch IDMatch TimeGroup B
A1 D
B2 E
C307/07/2019F
D1 A
E2 B
F 07/07/2019C

 

Kind Regards,

Riva

Labels (1)
1 Reply
chrismarlow
Specialist II
Specialist II

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;