topic Use joined columne for further calculations after Join in Connectivity & Data Prep

Use joined columne for further calculations after Join

Porto — Wed, 06 Oct 2021 09:44:56 GMT

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

Re: Use joined columne for further calculations after Join

marcus_sommer — Wed, 06 Oct 2021 10:33:43 GMT

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);

- Marcus

Re: Use joined columne for further calculations after Join

Porto — Wed, 06 Oct 2021 11:18:36 GMT

Dear Marcus!

Thank you so much for your help!

I have tried your mapping recommodation and it worked well!

all the best

David