Regarding to get distinct from different table and concatenatet to get required o/p

[qliksus]

Hi,

I have two table. One table contain ID from 101 to 105 and corresponding to id one value is there. Same way in the second table data is there but ID is from 101-107 i: 2 IDs are more. So now i want to create a table which have all the seven id but for 101-105 , value will come from first table and for the rest 2 ID i:e 106-107 value will come from second table . I have tried the Not Exist function it couldn't give me the correct answer. So can anyone help me to find out the solution????

My required o/p is

orderno, value

101,200

102,225

103,300

104,500

105,200

106,80

107,60

[#2 · Accepted solution]

Something like this:

Table1:

LOAD * INLINE [
 ORDERNO, itemcde, value
 101, K1, 200
 102, K2, 225
 103, K3, 300
 104, K4, 500
 105, K5, 200
 ];


LOAD * INLINE [
 ORDERNO, itemcde, value
 101, L1, 100
 102, L3, 115
 103, L5, 80
 104, L14, 300
 105, L7, 90
 106, L8, 80
 107, L10, 60
]
where not exists(ORDERNO, ORDERNO);

[qliksus]

Hi Nick,

Thanks for solution.

[qliksus]

Hi Nick,

This Where not exist(orderno,orderno) works foronly when we want to do operation on 2 INLINE table. But when i try samething in two resident table which comes from INLINE table the function doesn't work. Can you give some idea when it is the case of Resident tables, Live Data base tables?????

Can you go through the attached qvw file and solve the function and send me back?????

[#5]

Something like this:

Table1:

LOAD * INLINE [
 ORDERNO, itemcde, value
 101, K1, 200
 102, K2, 225
 103, K3, 300
 104, K4, 500
 105, K5, 200
 ];

Table2:
noconcatenate
LOAD * INLINE [
 TempORDERNO, itemcde, value
 101, L1, 100
 102, L3, 115
 103, L5, 80
 104, L14, 300
 105, L7, 90
 106, L8, 80
 107, L10, 60
]
;

concatenate (Table1)
load
 TempORDERNO as ORDERNO
 ,itemcde
 ,value
resident Table2
where not exists(ORDERNO, TempORDERNO);

drop table Table2;

See updated doc in attachment.