topic Re: Two tables, rows gets multiplied in the 2nd table in QlikView

Two tables, rows gets multiplied in the 2nd table

Wed, 25 Nov 2020 16:16:04 GMT

I have two tables:

83 Rows in one 85 in the other. Im trying to make a 3rd table that only shows those 2 rows diffing.

But the 2nd tables gets hundreds and hundreds of rows its like it get multiplied for every row.

i will attach my .Qvw and see if someone can hlep me.

I would also like to know what i'm doing wrong 😕

Thanks guys!

Meddelandet redigerades av: Niklas Söderqvist Uppdated .Qvw

Meddelandet redigerades av: Niklas Söderqvist updated qvw

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 10:39:56 GMT

Hi,

First off at the moment your two tables are not linked in the data model so by bringing 'row' in to your second table, you're creating a Cartesian join which is exactly what you say, every row is joining.

What fields are you looking to test?

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 10:41:55 GMT

None they are just from 2 different sources. I want to see the exact same amount of rows wiht same information in both.

And then in the 3rd table IF it doesnt match if one table has one more row than the other, then it should show only that row in the 3rd table.

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 10:44:35 GMT

Oh sorry didnt see that I used RowNo() in both tables I removed that linked one and added a EOD_RowNo and got my expected result.

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 10:45:09 GMT

Try loading the table second table using where not exists :

Table1:

Load

Key,

from abc.xls;

Table2:

Load

Key,

from pqr.xls

where not exists(Key);

Drop Table Table1;

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 10:46:06 GMT

OK, so in your second table remove the column 'row' as this doesn't belong to this table.

You already have EOD.rowno() in there which is your row number.

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 10:47:43 GMT

Exactly working fine now! Thanks Now I need to make my where not exist work. Any ideas?

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 10:54:33 GMT

Ok so to get your third table then firstly I would rename EOD.rowno() something better, re-use your RowNo() as row so you get EOD.row

Then you could run this (might need some tweaking sorry written without testing):

table3:

LOAD *

resident EOD

where not exists (EOD.row,row);

Concatentate

LOAD *

resident marketplacedeals

where not exists (row,EOD.row);

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 11:07:10 GMT

This doesnt work field not found row

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 11:13:51 GMT

can you add your latest qvw and I will take a look

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 11:17:38 GMT

Updated Qvw in my post now thanks so much!

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 11:47:13 GMT

Struggling to figure this one out!

Can't make it work with your tables, but if I add the Qlik example it works fine!

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterRecordFunctions/Exists.htm

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 11:52:53 GMT

Ok only thing I can suggest trying is to remove the qualification and rename the fields appropriately manually, e.g. eod_row

AFAIK this should work so the table qualification is the only thing I can think it might not like!

table3:

LOAD *

resident EOD

where not Exists (EOD.row, row);

Concatenate

LOAD *

resident marketplacedeals

where not Exists (row, EOD.row);

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 11:58:38 GMT

still doesnt work says field not found

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 13:59:14 GMT

Do you have any idea how to make a key and compare that key to the other table?

If I can look at: eod_SD&eod_Symbol&eod_Side&eod_Price

And compare those fields to the other table it should work. Just cant get the synbtax right :S

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 14:12:06 GMT

I haev created a key now with the uniquely identiying fields, but it gives me error if I use where not exists in the other table.

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 14:36:26 GMT

You shouldn't need the key though as row and EOD.row are your key fields in theory.

Something weird here, I am missing the way that exists should work.... I don't quite get why the help example works but your data does not.

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 15:02:17 GMT

Could it be that I have 3 tables so to speak not joined into 1 full table?

Re: Two tables, rows gets multiplied in the 2nd table

adamdavi3s — Thu, 24 Nov 2016 15:06:51 GMT

Have you got a new qvw where you have taken the 'qualify' off?

I just tried this again with the help example and it does NOT work when qualify is used

Re: Two tables, rows gets multiplied in the 2nd table

Thu, 24 Nov 2016 15:15:43 GMT

I have removed the qualify yes. see my latest qvw in original post.

But it is not working.press ctrl+t youll see the tablestructure.