Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
krithikarees
Contributor III
Contributor III

Two fact table

Hello All, I am stuck with a complex problem. Do any of you know how I can work my way around it?

I have SHIPDATE and RETDATE in two fact tables. I should create two different tables

1st table : eg: If panel SHIPDATE(Shipped) on 01-01-2017 and was RETDATE (returned) on 01-02-2017 count as 1 for the month of  return.

2nd table : eg: If a panel SHIPDATE(Shipped) on 01-01-2017 and was RETDATE(returned) on 01-02-2017 count 1 for the month of Shipment.

This needs to be done with the total of shipment and return remaining the same but bucketed differently in each table.

Example of what I have :

1 Reply
felipedl
Partner
Partner

Dont quite understand your requirements.

You have sales data, so you have the shipping and return date on the same data and data table or are they separate?

If you only need to count:

  • numbers of shipped orders
  • numbers of returned orders (meaning in this case they've been shipped at a given point)

You can simply create some flags with those rules and apply a simple sum on the created field to create your fact tables.

SalesDim:

Load SaleId,

Value,

Date#(CreatedDate,'DD/MM/YYYY') as CreatedDate,

Date#(ShipDate,'DD/MM/YYYY') as ShipDate,

Date#(ReturnDate,'DD/MM/YYYY') as ReturnDate,

DealerId;

Load * Inline

[

SaleId,Value,CreatedDate,ShipDate,ReturnDate,DealerId

1,10000,01/01/2018,05/01/2018,09/01/2018,A

2,500,05/01/2018,09/01/2018,,A

3,9994,08/08/2018,01/09/2018,24/09/2018,B

4,3555,25/09/2018,,,C

];

SalesFact:

Load SaleId,

if(len(ShipDate)>0,1,0) as [Order shipped flag],

if(len(ShipDate)>0 and len(ReturnDate)>0,1,0) as [Order Returned flag]

Resident SalesDim;

With that, you can check in a simple graph the sum of each flag to check your metric.

See the attached QVW for a simple solution with some dummy data.

sample.png

In my example, I have 4 orders, 3 of which have been shipped and only 2 of those where returned.

Hope it helps.