Join tables

[#1]

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_];

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.

[#2]

I've pulled down your qvw, but thre isn't enough to work with, I don't really understand what your data looked like before you transformed it.

Can you revert it to the two table version you had when you initially posted, and then we might have a better chance of resolving the syntax problem.

[stephencredmond]

Hi David,

You could try something like this:

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

CurrMap:

Mapping Load autonumberhash256('UK',"No_"),"Currency Factor";

SQL SELECT Distinct "No_","Currency Factor"

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

WHERE "Posting Date" >= '2009-01-03 00:00:00' And "Currency Factor" <> 0;

SalesInvLine:

LOAD

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

'UK' AS NavCoLine,

"Document No_" as Doc_Number_Line,

"Amount", ApplyMap('CurrMap',autonumberhash256('UK',"Document No_"),1) * "Amount" as LCY_Amount

WHERE EXISTS (Doc_Number,"Document No_");

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

FROM [Company$Sales Invoice Line]

GROUP BY [Document No_];

Means that you can maintain the 2 tables separately which may have a performance benefit for you.

Regards,

Stephen

[#4]

hi,

probably this should work

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_];

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

thanks