Tables join

[#1]

Hi All,

My problem is Suppose I have two tables :

Table A :

ID Amount

ID_1 2

ID_2 10

ID_3 8

ID_4 3

Table B:

ID Amount

ID_5 12

ID_2 11

ID_7 3

ID_4 7

In the output i want only :

ID

ID_5

ID_7

I know its very simple, but i m just screwed and not getting solution for same. Please Help on the same

[#2]

Hi, try

TableB:

Load

........

From .....

Where NotExists(ID)

Good luck!

Rainer

[Miguel_Angel_Baeyens]

Hello,

I don't know if I got your requirement right, but if you only want to keep in table two those values that don't appear in table one, you can use the following

TableA:LOAD ID, AmountFROM TableA; TableB:NOCONCATENATE LOAD ID, AmountFROM TableB WHERE NOT EXISTS(ID);

Hope that helps

[#4 · Accepted solution]

I think i was not able to explain the problem correctly.

See Suppose i have same above two tables A and B in excel.

Now my report will be showing all those ID which in table B but not present in tables A. Actually they will be working as the NEW ID's.

OUTPUT :

ID Amount

ID_5 12

ID_7 3

[#5]

Hi,

just modified Miguels statement.

TableA:
LOAD ID, Amount
FROM TableA;

TableB:
NOCONCATENATE LOAD ID, Amount
FROM TableB WHERE NOT EXISTS(ID);

Drop TableA;

That should work.

Rainer

[Miguel_Angel_Baeyens]

Hi,

So I see several things here.

If possible, I'd create a flag in the script so you can easily identify which IDs in Table B are nor present in Table A (note that since I'm loading from INLINE I have to rename fields ID to IDA or IDB, and so with AmountA and AmountB, but loading from excel may not need that, since the IF() conditional would be built runtime.):

TableA:LOAD * INLINE [IDA, AmountAID_1, 2ID_2, 10ID_3, 8ID_4, 3]; TableB:NOCONCATENATE LOAD * INLINE [IDB, AmountBID_5, 12ID_2, 11ID_7, 3ID_4, 7]; TableBWithFlags:LOAD *, IF(NOT EXISTS(IDA, IDB), 1, 0) AS FLAGRESIDENT TableB; DROP TABLE TableB;

Now in your chart, the following expression should work:

Sum({< FLAG = {1} >} AmountB)

But what I think, provided you have QlikView version 9 or higher, using indirect set analysis with elemental functions will do it cleaner and probably faster:

Sum({< IDB -= P(IDA) >} AmountB)

Hope that helps.

[#7]

Hi

Use right join... Thn u wil get the answer what u need

[#8]

A:

LOAD ID as ID1, Amount FROM (biff, embedded labels, table is [Sheet1$]);

B:

LOAD ID , Amount FROM (biff, embedded labels, table is [Sheet1$])

where Not exists(ID1,ID);

Drop table A;

As told by my seniors(Bhaskar )

[#9]

Thanks to all of you for a quick response....

BUt still not working at my end.

Can any one send me a example .Qvw !!!

[Miguel_Angel_Baeyens]

Hello Aswhin,

Feel free to use the script posted above as an example. Then change my sample names for your actual field names.

Regards.