2 similar file comparison

[RogerG]

I have 2 excel files as follows

File 1

ID, Name, Age

1, General, 20

2, abe, 30

3, general 40

file 2

ID, Name, Age

1, Joe, 21

2, abe, 30

3, sam,41

what I am trying to do is get a table that shows the following

ID, Name, File1 age, file2 age

1, joe , 20,21

2, abe, 30,30

3, sam,40,41

so if the name in the first file is General, replace it with the name in the second file

what I am getting sofar is 

1,joe,20,-

1,general, -,21

FILE1:
NoConcatenate
LOAD
*,
IF(ID & '|' & NAME) AS PRIKEY
FROM
[c:\file1.xlsx]
(ooxml, embedded labels);

FILE2:
NoConcatenate
LOAD
*,
IF(ID & '|' & if(NAME = 'General', File1.Name, Name)) AS PRIKEY // I don't know how to substitute this name if the name is general
FROM
[c:\file2.xlsx]
(ooxml, embedded labels);

[sunny_talwar · Accepted solution]

Try this

File1:
LOAD * INLINE [
    ID, Name, Age
    1, General, 20
    2, abe, 30
    3, general, 40
];

File2:
NoConcatenate
LOAD * INLINE [
    ID, Name, Age
    1, Joe, 21
    2, abe, 30
    3, sam, 41
];

Left Join (File2)
LOAD ID,
	 Name as NameTmp,
	 Age as File1Age
Resident File1;

DROP Table File1;

FinalTable:
LOAD ID,
	 If(WildMatch(NameTmp, 'general'), Name, NameTmp) as Name,
	 File1Age,
	 Age as File2Age
Resident File2;

DROP Table File2;

[RogerG]

the assumption here is the ID is a unique key.  I should have put RandomNumber as the field name.  hence the combined key to join the 2 tables.  I am closer, but still have issues.

lets change the data in file 1 to be 

<code>

ID, Name, Age

1, General, 20

2, abe, 30

3, general 40

2, larry, 45

1, mary, 23

I am closer with your answer though, just don't have the join between the 2 tables at this time.  the goal is to identify what is missing from the second table, but through a conversion of systems, the names have changed.

[sunny_talwar]

I am not sure I follow.

[RogerG]

The problem with the code above, is it eliminates this real primary key

IF(ID & '|' & if(NAME = 'General', File1.Name, Name)) AS PRIKEY

the id and name are used to tie out other fields within each table, but the name must be mapped to the name in the second file as the first file name is no longer valid.

[RogerG]

I just modified the excel files to include the needed columns.  your answer was correct though, thanks