Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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;
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
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;
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
u r right