topic Re: Merging tables on Data warehouse or in qlikview? in QlikView

Merging tables on Data warehouse or in qlikview?

arixooo123 — Fri, 07 Apr 2017 09:00:37 GMT

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

Re: Merging tables on Data warehouse or in qlikview?

Anil_Babu_Samineni — Fri, 07 Apr 2017 09:16:56 GMT

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;

Re: Merging tables on Data warehouse or in qlikview?

arixooo123 — Fri, 07 Apr 2017 09:33:04 GMT

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?

Anil_Babu_Samineni — Fri, 07 Apr 2017 09:43:51 GMT

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;

Re: Merging tables on Data warehouse or in qlikview?

arixooo123 — Fri, 07 Apr 2017 09:52:18 GMT

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.

Re: Merging tables on Data warehouse or in qlikview?

arixooo123 — Fri, 07 Apr 2017 13:28:00 GMT

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?

Anil_Babu_Samineni — Fri, 07 Apr 2017 14:18:04 GMT

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