Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Tables join

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

1 Solution

Accepted Solutions
Not applicable

Tables join

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

10 Replies
Not applicable

Tables join

Hi, try

TableB:

Load

........

From .....

Where NotExists(ID)

Good luck!

Rainer

Tables join

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

Not applicable

Tables join

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

Not applicable

Tables join

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

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.

Not applicable

Tables join

Hi

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

Not applicable

Tables join

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 )

Not applicable

Tables join

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

Hello Aswhin,

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

Regards.