Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.

Tags (1)
12 Replies
Not applicable

Join tables

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
Valued Contributor II

Join tables

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

Join tables

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

Join tables


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

Join tables

hi david,

you can use

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

it should work now..

thanks

Not applicable

Join tables

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

Join tables

Hi david,

Pls post your application

Not applicable

Join tables

Application attached.

Thanks

Dave

Not applicable

Join tables

bump!

Community Browser