topic Re: i have 2 files? in QlikView

i have 2 files?

Anonymous — Fri, 24 Apr 2015 10:43:44 GMT

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?

Re: i have 2 files?

ankitaag — Fri, 24 Apr 2015 10:52:07 GMT

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;

Re: i have 2 files?

Ralf-Narfeldt — Fri, 24 Apr 2015 10:58:25 GMT

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

Re: i have 2 files?

ankitaag — Fri, 24 Apr 2015 11:01:55 GMT

May be I didnt understand the question correctly

Re: i have 2 files?

Anonymous — Fri, 24 Apr 2015 11:07:29 GMT

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 ?

Re: i have 2 files?

Ralf-Narfeldt — Fri, 24 Apr 2015 11:55:59 GMT

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