topic Re: 2 similar file comparison in QlikView

2 similar file comparison

RogerG — Thu, 10 Sep 2020 16:07:11 GMT

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

Re: 2 similar file comparison

sunny_talwar — Thu, 10 Sep 2020 16:33:15 GMT

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;

Re: 2 similar file comparison

RogerG — Thu, 10 Sep 2020 17:36:32 GMT

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.

Re: 2 similar file comparison

sunny_talwar — Thu, 10 Sep 2020 18:00:50 GMT

I am not sure I follow.

Re: 2 similar file comparison

RogerG — Thu, 10 Sep 2020 19:21:10 GMT

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.

Re: 2 similar file comparison

RogerG — Thu, 10 Sep 2020 22:08:51 GMT

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