topic Strange issue with dates from two fact tables in QlikView

Strange issue with dates from two fact tables

sifatnabil — Mon, 11 May 2015 07:26:11 GMT

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?

Re: Strange issue with dates from two fact tables

marcus_sommer — Mon, 11 May 2015 09:59:14 GMT

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

Re: Strange issue with dates from two fact tables

sifatnabil — Mon, 11 May 2015 10:02:42 GMT

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?

Re: Strange issue with dates from two fact tables

marcus_sommer — Mon, 11 May 2015 10:14:05 GMT

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

Re: Strange issue with dates from two fact tables

Mon, 11 May 2015 12:27:09 GMT

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

Re: Strange issue with dates from two fact tables

sifatnabil — Tue, 12 May 2015 02:48:51 GMT

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.

Re: Strange issue with dates from two fact tables

sifatnabil — Tue, 12 May 2015 02:50:59 GMT

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).

Re: Strange issue with dates from two fact tables

Tue, 12 May 2015 04:45:45 GMT

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

Re: Strange issue with dates from two fact tables

mohammadkhatimi — Tue, 12 May 2015 05:07:57 GMT

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

Regards,

Mohammad

Re: Strange issue with dates from two fact tables

marcus_sommer — Tue, 12 May 2015 07:47:25 GMT

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