Strange issue with dates from two fact tables

[sifatnabil]

Hi all,

I have a 2 fact tables, connected to a master calendar like below:

These two fact tables are unique in that "Revenue" fact has data for each day, however, "TRADESPLITS" does not.

I have done a Select in Field trigger for Product2 in "Revenue" table, such that selecting Product2 triggers the same selection in Product1 in TRADESPLITS.

For the Product EMEA in April 2015, TRADESPLITS has no data for 3rd and 6th April, whereas Revenue does. When I select the trigger field (Product2), the dates from CalendarDate2 de-select 3rd and 6th April - this causes the summation of revenues from the Revenue fact to be incorrect. How can I make it such that 3rd and 6th April stay in selection?

[marcus_sommer]

I think it's rather not possible to link these data directly with two normal fact-tables. Several fact-tables will be always cause potential problems. Therefore it's recommended to use a star-sheme model.

I your case you could add all missing date-values to your fact-tables or to concatenate both tables to one single fact-table (this is my preferred method to handle several fact-tables - it's easy within the script and fast within the gui).

- Marcus

[sifatnabil]

Thanks Marcus_Sommer‌, are you saying I should concatenate the Revenue table and TRADESPLITS table, even though all fields (except Product and Date) are different?

[marcus_sommer]

I would trying it. Concatenated tables could be in general asynchron whereby by only a few common fields a link-table model might be better. But the concat is very easy whereas the link-table needs more efforts (and could be done later as an optimizing).

- Marcus

[#5]

You can also think of concatenating the date fields and renaming them in the master calendar. like

Linktable:

LOAD distinct Calendardate2 as CommonDate

resident RevenueTable;

concatenate

LOAD distinct TradeSplitsDate as CommonDate

resident TradeSplits;

Now use this CommonDate to built your master calendar and make fields like below in master calendar to link with Facts

CommonDate as Calendardate2,

CommonDate as TradeSplits

Thanks,

Singh

[sifatnabil]

If I concatenate the 2 fact tables, it doesn't allow you to choose a product in 1 table, to be associated with the same product in the other table. I have tried several methods including LinkTables but I don't think this is really possible.

[sifatnabil]

Thanks itsangad‌ I tried this but it doesn't work. The issue is, selecting a Product in TRADESPLITS, where 3rd and 6th April are missing, automatically de-selects 3rd and 6th April from the Revenue fact table (even though these dates exist in the Revenue fact).

[#8]

Then Sifat, you need fill in data for the missing dates. check : Generating Missing Data In QlikView

Also, if you could attach a sample QVW file with same model, and expected results. That will help community members to give quick responses.

Hope this is helpful

Thanks,

Singh

[mohammadkhatimi]

Please post a sample data for quick responses and for correct result..

Regards,

Mohammad

[marcus_sommer]

There are two different aspects of this case. One is the linking between two fact-tables which only worked correct if you have a perfect key. If you concatenated those tables to one single table there isn't any link-issue anymore - but this solved not the case if there aren't records for certain data-areas.

If you really want to display NULL within charts you need to generate all missing data (for only to notice NULL isn't it necessary to do it you could see it through the qv color-sheme of green/white/gray). For this see to the link from itsangad.

- Marcus