Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
Thanks for your quick reply.
tauqueer wrote:
load (Amount/Currency Factor) as amount resident SalesInvHeader;
<div></div>
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
hi david,
you can use
left join (SalesInvHeader) load (Amount/Currency Factor) as amount resident SalesInvHeader;
it should work now..
thanks
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
Hi david,
Pls post your application
Application attached.
Thanks
Dave
bump!