Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Porto
Contributor
Contributor

Use joined columne for further calculations after Join

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

2 Replies
marcus_sommer

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
Contributor
Contributor
Author

Dear Marcus!

Thank you so much for your help!

I have tried your mapping recommodation and it worked well!

all the best

David