Single Table with Common Fields

[microwin88x]

Hello,

I have the following tables:

DOCNO	HKONT	LINE	AMOUNT1
101	222	1	250
101	222	2	250
103	333	1	200
103	333	2	300
103	333	3	300
105	555	1	350
105	555	2	750
110	999	1	150
112	777	1	350

DOCNO	HKONT	CONCEPT	AMOUNT2
101	222	X1	500
103	333	X2	850
108	888	X3	250
115	345	X4	750

What I need is to show only from both tables: DOCNO - HKONT - AMOUNT1 - AMOUNT2

So that I could have the following (with values that match with Table 1):

DOCNO	HKONT	AMOUNT1	AMOUNT2	DIFFERENCE
101	222	500	500	0
103	333	800	850	50
105	555	1000	-	1000
110	999	150	-	150
112	777	350	-	350

Do you know how could I do that in order to have all by script (also the Difference field)?

Thank you!!!

[kenphamvn]

Hi

Try This

TABLE_1:
Load *
Inline
[
DOCNO,HKONT,LINE,AMOUNT1
101,222,1,250
101,222,2,250
103,333,1,200
103,333,2,300
103,333,3,300
105,555,1,350
105,555,2,750
110,999,1,150
112,777,1,350
];


NoConcatenate
TABLE_2:
load *
Inline [
DOCNO,HKONT,CONCEPT,AMOUNT2
101,222,X1,500
103,333,X2,850
108,888,X3,250
115,345,X4,750
];


NoConcatenate
JOIN_TABLE:
Load
DOCNO,HKONT,SUM(AMOUNT1) AS AMOUNT1
Resident TABLE_1
GROUP BY DOCNO,HKONT;


LEFT Join(JOIN_TABLE)
LOAD DOCNO,HKONT,SUM(AMOUNT2) AS AMOUNT2
Resident TABLE_2
GROUP BY DOCNO,HKONT;


NoConcatenate
FINAL_TABLE:
LOAD 
DOCNO,HKONT,AMOUNT1,AMOUNT2
,FABS(AMOUNT1-If(IsNull(AMOUNT2),0,AMOUNT2)) AS DIFFERENCE
Resident JOIN_TABLE;


DROP Tables TABLE_1,TABLE_2,JOIN_TABLE;

Regards

An Pham

[tamilarasu · Accepted solution]

Hello,

T1:
Load DOCNO,
     HKONT,
     Sum(AMOUNT1) as AMOUNT1
FROM
[https://community.qlik.com/thread/289350]
(html, codepage is 1252, embedded labels, table is @1)
Group by DOCNO, HKONT;

Left Join (T1)
T2:
Load DOCNO,
     AMOUNT2
FROM
[https://community.qlik.com/thread/289350]
(html, codepage is 1252, embedded labels, table is @2); 

Result:
Load *,
RangeSum(AMOUNT2,If(Isnull(AMOUNT2),AMOUNT1,-AMOUNT1)) as DIFFERENCE
Resident T1; 

DROP Table T1;

[shiveshsingh]

Please use below script.

A:LOAD * INLINE [
F1,HKONT, LINE,AMOUNT1 
101, 222, 1, 250
101, 222, 2, 250
103, 333 ,1 ,200
103, 333, 2, 300
103, 333, 3, 300
105, 555, 1, 350
105, 555, 2, 750
110, 999, 1, 150
112, 777, 1 ,350
];

C:
load sum(AMOUNT1) AS AMOUNT1,F1,HKONT
Resident A
GROUP BY F1,HKONT;
Drop table A;


left join
LOAD * INLINE [
DOCNO,HKONT,CONCEPT,AMOUNT2
101 ,222, X1 ,500
103 ,333, X2, 850
108, 888 ,X3 ,250
115 ,345, X4, 750];

NoConcatenate
Final:
load F1,HKONT,AMOUNT1,DOCNO,CONCEPT,AMOUNT2, IF(IsNull(AMOUNT2),AMOUNT1,AMOUNT2-AMOUNT1) AS Difference
Resident C;

drop table C

[arvind1494]

u r right