topic Re: Problem while retrieving data linked to a Canonical date in QlikView

Problem while retrieving data linked to a Canonical date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am having a problem while creating a canonical date. I don't know whether it is about the creation of canonical date itself or data modeling.Canonical Date

Below is the data Structure before creation of canonical date:

Now there is the data after the creation of canonical date:

For looking a the tables everything seems ok, so far.

Here is the script to date creation:

Datelink:

load

[S/N],

DATE_CASH as Date,

'CASH' as DateType

Resident CASHFLOW;

load

[S/N],

Date_COST as Date,

'Cost' as DateType

Resident COST;

Call CalendarFromField('Date', 'CommonCalendar','');

Call CalendarFromField('Date_COST', 'CostCalendar','Cost');

Call CalendarFromField('DATE_CASH', 'RevenueCalendar','Revenue');

Now what I would like to do is to show values of CASHFLOW and US$ fields for a chosen date, which comes from common calendar, provided by datelink.However I am getting the sum of all the years replicated in a chart as below:

Dimension: YEAR

Expressions:

sum({$<DateType={'COST'}>}US$)

sum( {$<DateType={'CASH'}>}CASHFLOW )

Does anyone know what is the problem?

Thank you!

Re: Problem while retrieving data linked to a Canonical date

swuehl — Mon, 06 Jun 2016 18:38:58 GMT

Is S/N granular enough to represent the single Amounts of COST and CASH on these dates?

Compare to HIC's blog post, where the link between the DateLink table and the facts is OrderLineID.

Re: Problem while retrieving data linked to a Canonical date

Anonymous — Mon, 06 Jun 2016 18:56:40 GMT

Yes, it is all granular, as seen above.

Re: Problem while retrieving data linked to a Canonical date

swuehl — Mon, 06 Jun 2016 19:14:56 GMT

If you select a value in Date field, only the same value should be possible (white) in fields Date_COST / DATE_CASH.

I believe you will see a lot more values, because your S/N field is not granular enough to link the DateLink table with your fact tables. You would need to use something like a record ID as key field.

Re: Problem while retrieving data linked to a Canonical date

Anonymous — Mon, 06 Jun 2016 19:25:42 GMT

You were right. In the case above I selected the year of 2016 for DATE_CASH / Date_COST. Now, when I select the Year in Canonical Date it appears as below:

Do you have an Idea where I put a new ID key? COST table or CASHFLOW table?

Re: Problem while retrieving data linked to a Canonical date

swuehl — Mon, 06 Jun 2016 19:30:01 GMT

What is ID_CASH and ID_COST?

Have you considered concatenating your fact tables?

Re: Problem while retrieving data linked to a Canonical date

Anonymous — Mon, 06 Jun 2016 19:40:37 GMT

ID_CASH = 'ACTUAL' or 'PAST'

ID_COST = RowNo()

Not Yet, But I will try to do that.

Re: Problem while retrieving data linked to a Canonical date

swuehl — Mon, 06 Jun 2016 19:45:42 GMT

Try concatenating your two fact tables and while concatenating, create a new field Date by renaming your cost resp. cash date field (you then don't need the DateLink table).

Re: Problem while retrieving data linked to a Canonical date

Anonymous — Mon, 06 Jun 2016 20:05:09 GMT

Thank you. It solved my problem.