topic Tables join in QlikView

Tables join

Tue, 01 Mar 2011 09:49:22 GMT

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_5

ID_7

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

Tables join

Tue, 01 Mar 2011 09:59:00 GMT

Hi, try

TableB:

Load

........

From .....

Where NotExists(ID)

Good luck!

Rainer

Tables join

Miguel_Angel_Baeyens — Tue, 01 Mar 2011 09:59:24 GMT

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

Tables join

Tue, 01 Mar 2011 10:06:35 GMT

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

Tables join

Tue, 01 Mar 2011 10:47:19 GMT

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

Tables join

Miguel_Angel_Baeyens — Tue, 01 Mar 2011 10:48:36 GMT

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.

Tables join

Tue, 01 Mar 2011 10:53:23 GMT

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

Tables join

Tue, 01 Mar 2011 10:57:06 GMT

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

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

where Not exists(ID1,ID);

Drop table A;

As told by my seniors(Bhaskar )

Tables join

Tue, 01 Mar 2011 12:46:38 GMT

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

BUt still not working at my end.

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

Tables join

Miguel_Angel_Baeyens — Tue, 01 Mar 2011 12:55:40 GMT

Hello Aswhin,

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

Regards.

Tables join

Tue, 01 Mar 2011 13:00:46 GMT

Hey Thxx !!!!

It worked actually there was mistake done by me at script level................:) Thanks Again to all