Use joined columne for further calculations after Join

[Porto]

Dear Community!

First of all i want to thank you for you time and efforts and want to let you know that i am new to this community and don't know the traditions/practicies yet, so please, if i am missbehaving in any form, don't hesitate to give me feedback or let me know, which information are essential for helping me.

Unfortunately, i do have a problem (obviously):

I going to create a dasbord for our sales 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);

Can you tell me, where do i have the problem, respectively what i need to consider.

Thank you in advance for your efforts and all the best

David

[marcus_sommer · Accepted solution]

It are two separate loads and within the first load the field DeliveredQuantity didn't exists and therefore the load failed. This means you need another following load to access this additional field.

An alternatively to your join approach may be to use a mapping, maybe like this:

m: mapping LOAD (FIRM_NR&'|'&AUFK_NR&'|'&AUFP_NR), AUFL_MENGE
FROM [lib://QVD/HKTRS2_V_AUFL.qvd] (qvd);

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) *
applymap('m', FIRM_NR&'|'&AUFK_NR&'|'&AUFP_NR, 0) AS TurnoverOrderPosition,
FROM [lib://QVD/HKTRS2_V_AUFP.qvd] (qvd);

- Marcus

[Porto]

Dear Marcus!

Thank you so much for your help!

I have tried your mapping recommodation and it worked well!

all the best

David