Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rassanbekov
Partner - Contributor
Partner - Contributor

Difference in data loaded

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?

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
77605787872843230
7770977701389270

Good luck

Andrew

View solution in original post

15 Replies
swuehl
MVP
MVP

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

petter
Partner - Champion III
Partner - Champion III

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

effinty2112
Master
Master

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 11
61 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

petter
Partner - Champion III
Partner - Champion III

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:

2017-08-20 12_48_59-QlikView x64 - [C__Users_Petter_OneDrive_Documents_JOIN.qvw].png

Look at the attach QlikView app for a full example.

rassanbekov
Partner - Contributor
Partner - Contributor
Author

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,

rassanbekov
Partner - Contributor
Partner - Contributor
Author

I attached input data. Please see below

rassanbekov
Partner - Contributor
Partner - Contributor
Author

I attached input data. Please see below

rassanbekov
Partner - Contributor
Partner - Contributor
Author

I attached input data. Please see below

rassanbekov
Partner - Contributor
Partner - Contributor
Author

I attached input data. Please see below