Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

anselmo_meira
Contributor II

Problem while retrieving data linked to a Canonical date

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:

test.PNG

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

After.PNG

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:

Untitled.png

Dimension: YEAR

Expressions:

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

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

Does anyone know what is the problem?

Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Problem while retrieving data linked to a Canonical date

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

8 Replies
MVP
MVP

Re: Problem while retrieving data linked to a Canonical date

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.

anselmo_meira
Contributor II

Re: Problem while retrieving data linked to a Canonical date

SErial.png

Yes, it is all granular, as seen above.

MVP
MVP

Re: Problem while retrieving data linked to a Canonical date

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.

anselmo_meira
Contributor II

Re: Problem while retrieving data linked to a Canonical date

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:

CApture clear.PNG

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

Highlighted
MVP
MVP

Re: Problem while retrieving data linked to a Canonical date

What is ID_CASH and ID_COST?

Have you considered concatenating your fact tables?

anselmo_meira
Contributor II

Re: Problem while retrieving data linked to a Canonical date

ID_CASH = 'ACTUAL' or 'PAST'

ID_COST = RowNo()


Not Yet, But I will try to do that.

MVP
MVP

Re: Problem while retrieving data linked to a Canonical date

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

anselmo_meira
Contributor II

Re: Problem while retrieving data linked to a Canonical date

Thank you. It solved my problem.