topic Difference in data loaded in QlikView

Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 08:22:06 GMT

Hello!

i have two data set with key in ID:

the total number of lines in both data sets are 502,547.

However, when I load in Qlik, the total number of lines in straight table shown as 502,549.

How can I identify the difference why click loads more than actually exists?

Maybe any scripts exists to identify?

Re: Difference in data loaded

swuehl — Sun, 20 Aug 2017 10:11:11 GMT

How do you load the two data sets, i.e. how does your script and the data model look like?

Re: Difference in data loaded

petter — Sun, 20 Aug 2017 10:24:03 GMT

This example show how you can JOIN the two tables in a load script and then find the missing keys from each of the tables by doing loads that check for nulls:

T1:
LOAD * INLINE [
K, D1
A, 1
B, 2
C, 3
D, 4
E, 5
G, 6
K, 7
];
JOIN
LOAD * INLINE [
K, D2
A, 10
B, 20
D, 40
F, 50
H, 60
J, 70
];


[Missing from table #2]:
LOAD
  K AS K1
RESIDENT
  T1
WHERE
  IsNull(D2);
  
[Missing from table #1]:
LOAD
  K AS K2
RESIDENT
  T1
WHERE
  IsNull(D1);

Re: Difference in data loaded

effinty2112 — Sun, 20 Aug 2017 10:34:28 GMT

Hi Ruslan,

You could try a strategy like this. Make a new qvw and load a couple of two column tables with ID and a column identifying the source. In the example below I've called the sources Table A and Table B. Concatenate the tables then a straight table in the front will tell you of values of ID that are not common to both tables.

Data:

LOAD * INLINE [

ID, Table A

1, 1

2, 1

3, 1

4, 1

6, 1

];

LOAD * INLINE [

ID, Table B

1, 1

2, 1

3, 1

4, 1

5, 1

];

Now this straight table with dims ID, Table A, Table B and expression as shown. Make sure the option to suppress zero values is checked.

ID	Table A	Table B	2-Rangesum([Table A] , [Table B])
5		1	1
6	1		1

The table tells us that for the example above ID = 5 is only present in Table B, ID = 6 is only in Table A.

Hope this helps.

Andrew

Re: Difference in data loaded

petter — Sun, 20 Aug 2017 10:50:38 GMT

If you prefer do list the missing keys from each table solely in the UI in two straight tables you can disregard the last two loads in the script I showed you and do like this:

Look at the attach QlikView app for a full example.

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 11:25:47 GMT

Please see input data enclosed.

When I simply upload these data into Qlikview, the number of raws in straight table 502,549, however files are identical and the maximum number of lines 502,547.

Hope you can resolve the issue in qvw extension...

Kind regards,

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 11:27:33 GMT

I attached input data. Please see below

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 11:27:40 GMT

I attached input data. Please see below

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 11:27:48 GMT

I attached input data. Please see below

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 11:28:00 GMT

I attached input data. Please see below

Re: Difference in data loaded

antoniotiman — Sun, 20 Aug 2017 12:07:46 GMT

This is because there are 2 duplicated ID

7760578787-7770977701

Regards,

Antonio

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 13:13:56 GMT

Oh.. great!

How did you identified these duplicates... Please explain your approach

Re: Difference in data loaded

effinty2112 — Sun, 20 Aug 2017 13:18:13 GMT

Hi Ruslan,

this script will give the row no in the source of the second occurrence of the duplicate ID.

[Table 1]:

LOAD ID,

MONTH0

FROM

[Table 1.xlsx]

(ooxml, embedded labels, table is Лист1);

Inner Join([Table 1])

LOAD

RowNo(),

FirstSortedValue(ID,RecNo(),2) as ID

Resident [Table 1]

Group by ID;

ID	RowNo()	MONTH0
7760578787	284323	0
7770977701	38927	0

Good luck

Andrew

Re: Difference in data loaded

rassanbekov — Sun, 20 Aug 2017 13:36:13 GMT

Could you please share your qvw file since the error occurs when I use your scripts...

Re: Difference in data loaded

antoniotiman — Sun, 20 Aug 2017 13:41:55 GMT

I have loaded Tables with (or without) Join, then

=Concat({<ID={'=Count(ID) > 1'}>} DISTINCT ID,'-')

Re: Difference in data loaded

petter — Sun, 20 Aug 2017 14:09:50 GMT

Do determine repeated use of ID on each table you can simple load the two tables like this and have a straight table to show the IDs that occur more than once: