topic Join tables in QlikView

Join tables

Thu, 29 Apr 2010 11:57:22 GMT

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.

Join tables

Thu, 29 Apr 2010 12:06:10 GMT

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

Join tables

sparur — Thu, 29 Apr 2010 12:07:20 GMT

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.

Join tables

Thu, 29 Apr 2010 12:13:17 GMT

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

Join tables

Thu, 29 Apr 2010 12:37:29 GMT


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

Join tables

Thu, 29 Apr 2010 13:08:55 GMT

hi david,

you can use

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

it should work now..

thanks

Join tables

Thu, 29 Apr 2010 13:42:23 GMT

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

Join tables

Thu, 29 Apr 2010 14:21:57 GMT

Hi david,

Pls post your application

Join tables

Fri, 30 Apr 2010 09:49:52 GMT

Application attached.

Thanks

Dave

Join tables

Fri, 30 Apr 2010 14:41:20 GMT

bump!

Join tables

Fri, 30 Apr 2010 18:35:47 GMT

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.

Join tables

stephencredmond — Fri, 30 Apr 2010 19:29:24 GMT

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

Join tables

Sun, 02 May 2010 08:49:56 GMT

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