Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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 

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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) 

View solution in original post

6 Replies
Highlighted

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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?

Highlighted

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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) 

View solution in original post

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)