Use joined column for further calculations after Join in Qlik Sense

Question

I am going to create a dashbord for our self activities and therefore have joined two fact tables.

Now, i want to make further calculations with the new columne but QlikSense shows an error and indiacted that the columne "DeliveredQuantity" couldn't be found.

I will show you my statement:

ORDERPOSITION:
LOAD
AUFK_NR&'|'&FIRM_NR as %AUFK,
(FIRM_NR&'|'&AUFK_NR&'|'&AUFP_NR) AS %AUFPdelivered,
AUFP_NR,
ART_NR as %ART_NR,
ART_NR,
(AUFP_PREIS2/AUFP_PEHFAKTOR/AUFP_PREISFAKTOR)*DeliveredQuantity AS TurnoverOrderPosition,
FROM [lib://QVD/HKTRS2_V_AUFP.qvd]
(qvd);
 
LEFT JOIN (ORDERPOSITION)
LOAD (FIRM_NR&'|'&AUFK_NR&'|'&AUFP_NR) AS %AUFPdelivered,
AUFL_MENGE AS DeliveredQuantity
FROM [lib://QVD/HKTRS2_V_AUFL.qvd]
(qvd);

score 1 · Answer 1 · answered Oct 19 '21 at 04:11

You have to first join the tables. Once the tables are joined and all fields are in the same table, then the field can be used to perform the calculation

Something like this:

// First step is to join both tables
// Prepare the calculated field TurnoverOrderPosition_Temp
ORDERPOSITION_Temp:
Load
    AUFK_NR & '|' & FIRM_NR as %AUFK,
    FIRM_NR & '|' & AUFK_NR & '|' & AUFP_NR AS %AUFPdelivered,
    AUFP_NR,
    ART_NR as %ART_NR,
    ART_NR,
    AUFP_PREIS2 / AUFP_PEHFAKTOR / AUFP_PREISFAKTOR AS TurnoverOrderPosition_Temp,
From 
    [lib://QVD/HKTRS2_V_AUFP.qvd]
    (qvd)
;
 
Left Join (ORDERPOSITION)
Load
    FIRM_NR & '|' & AUFK_NR & '|' & AUFP_NR AS %AUFPdelivered,
    AUFL_MENGE AS DeliveredQuantity
From 
    [lib://QVD/HKTRS2_V_AUFL.qvd]
    (qvd)
;

// Once the tables are joined then we can
// create the final calculation
ORDERPOSITION:
Load 
    *,
    TurnoverOrderPosition_Temp * DeliveredQuantity as TurnoverOrderPosition
Resident
    ORDERPOSITION_Temp
;

// we dont need this table anymore
Drop Table ORDERPOSITION_Temp;
// we dont need this field anymore
Drop Field TurnoverOrderPosition_Temp From ORDERPOSITION;

Use joined column for further calculations after Join in Qlik Sense

1 Answers1