topic Re: Data Problem in QlikView

Data Problem

Wed, 06 Apr 2016 19:15:45 GMT

Please find the below sample data.

File1

field1	field2	field3
Tom	Auli	Abel
Tom	Auli	Lee
Anel	Race	Race
Anel	Brady	Reece
Anel	soman	swetha

File2

field1	field 2	field 3
Tom	Auli	Andy
Tom	Auli	Lee
Anel	Simpson	Race
Anel	soman	agrawal
Anel	Elvy	swetha

so what i would like to see now is 1 object that has a list of the differences (and a total number in the heading) ex. 15 mismatched file1 hierarchy then if an item is selected from the mismatch list - two objects display

1) the data from the File1

2) the data from the file2

i need total 3 objects:

1.list of difference

2.full data from file1 (5 rows)

3.full data from file2 (5 rows)

If we have one item in one source and it is not there in another.. shouldn't it be highligted as difference..

if they are not the same in both files then they should be listed as different - ideally flagged as only in one of the files

any suggestion please

Thanks

Re: Data Problem

swuehl — Wed, 06 Apr 2016 19:42:05 GMT

Maybe have a look at

How to compare 2 tables and put non-matching results?

Re: Data Problem

Wed, 06 Apr 2016 20:15:02 GMT

Thanks for reply.

here my question is on what base i have to compare the data because in this data the values from field3 is changing in some cases when field1 and field2 are same at the same time the values from field2 also changing when field1 and field3.

To compare any data one to one relation should be there..right???

let me know if iam wrong

Re: Data Problem

swuehl — Wed, 06 Apr 2016 21:19:09 GMT

Well, to define 'difference' I think we need to define which entities to compare, I assume we want to compare field1 to field1, field2 to field2 on a per record base, where we create a record number / ID that starts from the beginning of the table.

Modifying John's script:

Table:
LOAD *,
Recno() as RecID,
dual('Table 1',1) as Table;
LOAD field1, 
     field2, 
     field3
FROM
[https://community.qlik.com/thread/212357]
(html, codepage is 1252, embedded labels, table is @1);

CONCATENATE
LOAD *,
Recno() as RecID,
dual('Table 2',2) as Table;
LOAD field1, 
     [field 2] as field2, 
     [field 3] as field3
FROM
[https://community.qlik.com/thread/212357]
(html, codepage is 1252, embedded labels, table is @2);

INNER JOIN (Table)
LOAD RecID
,if(count(RecID)<2,-1) as "Only in One Table?"
,if(maxstring(field1)<>minstring(field1),-1) as "field1 Different?"
,if(maxstring(field2)<>minstring(field2),-1) as "field2 Different?"
,if(maxstring(field3)<>minstring(field3),-1) as "field3 Different?"
RESIDENT Table
GROUP BY RecID
;

Then creating tables that show the differences hightlighted and also two tables for the original data:

This should just give you an idea how you could do this.

iOr maybe just export your tables to csv files and use a diff tool to show the differences?

Re: Data Problem

Thu, 07 Apr 2016 18:40:51 GMT

thanks

its working fine.but i want separate object for only Difference value(list of difference) along with both files(file1 and file2).

Difference(RecID)

as a separate object which are only have difference.So if i click on any item from this list the difference values should be shown from both the files

Re: Data Problem

swuehl — Thu, 07 Apr 2016 19:35:11 GMT

The data is already there, you just need to create a listbox that shows RecID filtered by the records with differences.

See attached for an example (and I think the objects that show all original files and only the differences are already there, right?)

Re: Data Problem

Thu, 07 Apr 2016 23:27:02 GMT

here i am facing problem with RecID becoz the sorting path is different from one file to second file like RedID 23 in file1 is RecID 40 in file2 so difficult to prepare with RecID

And now my requirement is to see two objects that has only difference(both field2 and field3 separately)

list of field2 Difference list of field3 Difference

Auli Abel

Race Agrawal

Brady swetha

Soman Andy

Elvy Reece

Simpson

Re: Data Problem

swuehl — Wed, 13 Apr 2016 18:18:58 GMT

If your request is resolved, please consider to close also this thread by marking any helpful or correct answer.

Thank you,

Stefan

Re: Data Problem

Wed, 13 Apr 2016 18:33:07 GMT

Sure

Thanks