Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi, try
TableB:
Load
........
From .....
Where NotExists(ID)
Good luck!
Rainer
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
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
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
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.
Hi
Use right join... Thn u wil get the answer what u need
A:
LOAD ID as ID1, Amount FROM
B:
LOAD ID , Amount FROM
where Not exists(ID1,ID);
Drop table A;
As told by my seniors(Bhaskar )
Thanks to all of you for a quick response....
BUt still not working at my end.
Can any one send me a example .Qvw !!!
Hello Aswhin,
Feel free to use the script posted above as an example. Then change my sample names for your actual field names.
Regards.