Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

arixooo123
Contributor

Merging tables on Data warehouse or in qlikview?

Hi Guys,

I have two tables with many to one relationship (Sales Order Detail and Sales Order Header), I need to create a Sales Fact table and I am wondering whether I should do this on the DW side or while loading the tables in qlikview.

If I wanted to do it in qlik, what do you think I should do to divide the tax amount in Header table between the related rows in fact table?

I might come up with a SQL statement to do it in DW, but in qlik I am not really sure how I can do it.

Thank you so much for your answer

Regards

Ashanti 

Tags (3)
1 Solution

Accepted Solutions
arixooo123
Contributor

Re: Merging tables on Data warehouse or in qlikview?

I wrote the sql query for this for more clarification:

  SELECT  A.[OrderID]

      ,a.[ProductID]

      ,a.[UnitPrice]

      ,a.[Quantity] 

      ,b.[Freight]* ([UnitPrice]*[Quantity]/(select sum([UnitPrice]*[Quantity])  from [[order details] as c where c.orderid=b.orderid) ) as freight_of_detail

    

  FROM  [Order Details] as A

inner join [orders] as b on (a.orderid=b.orderid) 

6 Replies

Re: Merging tables on Data warehouse or in qlikview?

Fact is on top and then relation ship on bottom be help of Left Join like below.

Here, Data model looks only one single dimension table which directory called Location_Zip. Many to one relationship like below.

LZ:

Load * FROM Path;

Left Join(LZ)

Load * FROM Path1;

Left Join(LZ)

Load * FROM Path2;

Left Join(LZ)

Load * FROM Path3;

arixooo123
Contributor

Re: Merging tables on Data warehouse or in qlikview?

Thank you for your answer, But I am not sure that was the answer of my questions.

I had two questions:

1- Where should I merge the tables and why? (DW or Qlik )

2-If I wanted to merge them in qlik, how can I divide the amount of tax in Header table between the related rows in detail table?

Re: Merging tables on Data warehouse or in qlikview?

1) If you want to do in Qlik go and merge. I am not aware of DW

2) You can use by split like below. We can do anything with Qlik as technical prospective and may be work around atleast. If you provide more information we may help better

Header:

Load Field1, Field2, 'amount of tax' as Flag from HeaderTable;

RelatedRows:

Load Field1, Field2, 'rows related' as Flag from DetailTable;

arixooo123
Contributor

Re: Merging tables on Data warehouse or in qlikview?

Consider the attached image, I have two tables and I want to merge them while loading in Qlik. When joining the tables, Freight should be broken down into all the joined rows of Detail table. How to divide the amount of freight between the transactions in sales detail table when joining in qlik. Quest.png

arixooo123
Contributor

Re: Merging tables on Data warehouse or in qlikview?

I wrote the sql query for this for more clarification:

  SELECT  A.[OrderID]

      ,a.[ProductID]

      ,a.[UnitPrice]

      ,a.[Quantity] 

      ,b.[Freight]* ([UnitPrice]*[Quantity]/(select sum([UnitPrice]*[Quantity])  from [[order details] as c where c.orderid=b.orderid) ) as freight_of_detail

    

  FROM  [Order Details] as A

inner join [orders] as b on (a.orderid=b.orderid) 

Re: Merging tables on Data warehouse or in qlikview?

It's possible because, You have one common field on your two tables. We can merge it in

Community Browser