Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join tables

Hello,

I'm new to qlikview and have a question about joining two tables.

This is what I currently have :

SalesInvHeader:

LOAD

autonumberhash256('UK',"No_") as Key_Inv,

autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

'UK' AS NavCo,

"No_" as Doc_Number,

"Posting Date" AS Date,

"Sell-to Customer No_",

"Sell-to Customer Name",

if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

;

SQL SELECT *

FROM "SERVER".dbo."Company$Sales Invoice Header"

WHERE "Posting Date" >= '2009-01-03 00:00:00';

SalesInvLine:

LOAD

autonumberhash256('UK',"Document No_") as Key_Inv,

'UK' AS NavCoLine,

"Document No_" as Doc_Number_Line,

"Amount"

WHERE EXISTS (Doc_Number,"Document No_");

SQL SELECT [Document No_], SUM(Amount) AS Amount

FROM [Company$Sales Invoice Line]

GROUP BY [Document No_];

error loading image

I then need to calculate Amount (LCY)=(Amount/Currency Factor) but am unsure how to do it when the values are on separate tables, ideally I would just like to end up with one table.

12 Replies
Not applicable
Author

hi,

you can use left join as follows

SalesInvHeader:

LOAD

autonumberhash256('UK',"No_") as Key_Inv,

autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

'UK' AS NavCo,

"No_" as Doc_Number,

"Posting Date" AS Date,

"Sell-to Customer No_",

"Sell-to Customer Name",

if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

;

SQL SELECT *

FROM "SERVER".dbo."Company$Sales Invoice Header"

WHERE "Posting Date" >= '2009-01-03 00:00:00';

SalesInvLine:

left join (SalesInvHeader) LOAD

autonumberhash256('UK',"Document No_") as Key_Inv,

'UK' AS NavCoLine,

"Document No_" as Doc_Number_Line,

"Amount"

WHERE EXISTS (Doc_Number,"Document No_");

SQL SELECT [Document No_], SUM(Amount) AS Amount

FROM [Company$Sales Invoice Line]

GROUP BY [Document No_];

thanks



sparur
Specialist II
Specialist II

Hello David.

You should join 2 tables by field Key_Inv:

INNER JOIN(SalesInvLine)

LOAD

Key_Inv,

[Currency Factor] AS [Currency Factor Line]

RESIDENT SalesInvHeader;

than you can calculate Amount(LCY) in each Line.

Not applicable
Author

hi,

you can use left join as follows

SalesInvHeader:

LOAD

autonumberhash256('UK',"No_") as Key_Inv,

autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

'UK' AS NavCo,

"No_" as Doc_Number,

"Posting Date" AS Date,

"Sell-to Customer No_",

"Sell-to Customer Name",

if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

;

SQL SELECT *

FROM "SERVER".dbo."Company$Sales Invoice Header"

WHERE "Posting Date" >= '2009-01-03 00:00:00';

left join (SalesInvHeader) LOAD

autonumberhash256('UK',"Document No_") as Key_Inv,

'UK' AS NavCoLine,

"Document No_" as Doc_Number_Line,

"Amount"

WHERE EXISTS (Doc_Number,"Document No_");

SQL SELECT [Document No_], SUM(Amount) AS Amount

FROM [Company$Sales Invoice Line]

GROUP BY [Document No_];

load (Amount/Currency Factor) as amount resident SalesInvHeader;

thanks



Not applicable
Author


tauqueer wrote:

load (Amount/Currency Factor) as amount resident SalesInvHeader;


<div></div>
Thanks for your quick reply.

That has now now added the amount to the sales invoice header as i wanted, just a problem with the

load (Amount/Currency Factor) as amount resident SalesInvHeader; has now created another table called SalesInvHeader-1 that joins to the salesInvHeader on amount

Thanks

Not applicable
Author

hi david,

you can use

left join (SalesInvHeader) load (Amount/Currency Factor) as amount resident SalesInvHeader;

it should work now..

thanks

Not applicable
Author

Hi,

Sorry again,

I have changed it to AmountLCY as i want it showing as a new field.

left join (SalesInvHeader) load (Amount/"Currency Factor") as AmountLCY resident SalesInvHeader;

But now for some reason it's duplicating the results and not working out the AmountLCY correctly

Thanks

Not applicable
Author

Hi david,

Pls post your application

Not applicable
Author

Application attached.

Thanks

Dave

Not applicable
Author

bump!