Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
i have 2 xl files which contains one sheet like below
xlfile1:
sheet1
field1
field2
field3
field4
xlfile2:
sheet2
field1
field2
field3
field4
now i want make row wise comparision and want to identify between these to xl files?
sheet names is same and field names also same
but inside rows may have some different data ?
even above fields are not unique keys , all are having duplicate values?
Hi,
Do you want something like this :
xlfile1:
Load * Inline [
sheet1
field1
field2
field3
field4
];
xlfile2:
Mapping Load * Inline [
sheet2, data
field1,a
field2,b
field3,c
field4,d
];
T1:
Load sheet1,applymap('xlfile2',sheet1) as data
Resident xlfile1;
drop table xlfile1;
Qualify *;
Unqualify ID;
xlfile1:
Load *, RecNo() As ID from xlfile1;
xlfile2:
Load *, RecNo() As ID from xlfile2;
This supposes that you want to do a true row to row comparison between the sheets, so it's creating a ID from row number as key.
All fields but ID are qualified, so they will be separate fields (xlfile1.field1, xlfile2.field1 ...)
May be I didnt understand the question correctly
U MEAN i need to write like this
qualify *;
unqualify id;
xlfile1:
field1
field2
field3
field4
unqualify *;
xfile2:
field1
field2
field3
field4
rowno() as id
where not exists(id)
is this is the thing what u are telling ?
No, like I wrote. I don't know your exact Load statements, as that would depend on where you load them from, so they are simplified. A straight load of the two tables could look like this:
Directory;
LOAD * FROM xlfile1.xlsx (ooxml, embedded labels, table is Sheet1);
LOAD * FROM xlfile2.xlsx (ooxml, embedded labels, table is Sheet1);
This would concatenate the two tables.
Doing this:
Qualify *;
Unqualify ID;
Directory;
xlfile1:
LOAD *, RecNo() As ID FROM xlfile1.xlsx (ooxml, embedded labels, table is Sheet1);
xlfile2:
LOAD *, RecNo() As ID FROM xlfile2.xlsx (ooxml, embedded labels, table is Sheet1);
You get 2 tables with same fields, linked by ID which is the row number. Then you can compare row by row xlfile1.field1 with xlfile2.field1 and so on