Link Time Periods Table

[#1]

Hello All,

I know working with Time calendars is a pain in Qlikview and I am stuck with an similar issue here. I have a fact, Dimension and Time period Tables. The Time Period tables is standard across the organization and we have already used it for another application. Now in this application, we have a TIME_PERIOD_ID field in the Sales Table and TIME_PERIOD_ID in the TIME_PERIODS_TABLE. I need some suggestions as to how to link these, I think it creates a Link table which is fine and I think I am following the right process of linking them, but doesnt seem to work. Has anyone come across similar issue in the past? Using V9 SR7.

Thanks in Advance

[stephencredmond]

Hi,

If you have a TIME_PERIOD_ID in your Sales table and a TIME_PERIOD_ID in your calendar table then everything should work just fine.

When you say "doesn't seem to work", what exactly doesn't seem to work?

I would normally put a field from the Sales table, the TIME_PERIOD_ID field, and a field from the calendar table into a Table box - this will quickly reveal why there isn't a connection.

Regards,

Stephen

[ToniKautto]

QlikView is associative, which means it always will link fields with the same name as key fields.

Please attach a sample QVW file to make evaluation of your issue easier. In case your app contains sensitive data, please scramble the fields to make content unreadable.

Settings > Document Settings > Scrambling

[#4]

Hello I will explain the problem I have in a bit detail. I have 2 Facts table where I have a field TIME_PERIOD_ID and there are some values in that. I have another master calendar where there is a TIME_PERIOD_ID on the DB which is blank. Check below:

FACT1:
LOAD CUSTOMER_ID,
     PRODUCT_ID,
     ROUND(TIME_PERIOD_ID) as TIME_PERIOD_ID,
     INDIRECT_SALES_VOL,
     INDIRECT_SALES_REV,
     PIUK_SALES_REV,
     DATA_SOURCE,
     LAST_UPDATED_DATE
FROM
D:\QVApps\Sales\QVD\W_IRSDWH_WKLY_INDIRECT_SALES_F.QVD
(qvd);

FACT2:
CONCATENATE (W_IRSDWH_WKLY_INDIRECT_SALES_F)
LOAD DISTINCT CUSTOMER_ID,
     PRODUCT_ID ,
     ROUND(TIME_PERIOD_ID) as TIME_PERIOD_ID,
     INDIRECT_SALES_VOL as PER_INDIRECT_SALES_VOL,
     INDIRECT_SALES_REV as PER_INDIRECT_SALES_REV,
     PIUK_SALES_REV as PER_PIUK_SALES_REV,
     DATA_SOURCE,
     LAST_UPDATED_DATE
FROM
D:\QVApps\Sales\QVD\W_IRSDWH_PER_INDIRECT_SALES_F.QVD
(qvd);

CALENDAR:

LOAD TIME_PERIOD_ID,

     TIME_PERIOD_TYPE,

     TIME_PERIOD_YEAR,

     TIME_PERIOD_PERIOD,

     TIME_PERIOD_WEEK_NO,

     TIME_PERIOD_WEEK_DAY,

     TIME_PERIOD_START_DATE,

     TIME_RPT_PERIOD,

     TIME_RPT_WEEK,

FROM XYZ.QVD(qvd);

When I am joining them, then I can see that there are repeated values in the TIME_PERIOD_ID listbox and top time period values seem to be working for all the tables but the Time period itself and if I select the bottom time values, they work for only Time period Tables but not for other facts and dimensions.

Hope this helps.

Thanks

[johnw]

It might be a problem of the real value vs. the displayed value.  For instance, let's say your data is really this:

TIME_PERIOD_ID
1.1
1.2
2.7
3.4
3.8

When you round(), you change the actual values to this:

1
1
3
3
4

And in a list box, since you only see distinct values, you'd see this:

1
3
4

But let's say you loaded this same data in another table and didn't round() it, and instead just changed the numeric format.  Although it would still display as 1, 3 and 4 in charts, Qlikview still knows the underlying numeric value, and would give you separate rows in a list box, since they're really separate values.  In addition, they are distinct from the earlier values, since those had been rounded off.  So you'd see this in a list box:

1
1
1
3
3
3
4
4

Some of those values would apply to some tables, and others to others, just like you're seeing.  Now I can't say for certain that that's what's causing your problem, but it's certainly ONE way to cause that problem.

[ToniKautto]

Your key to solving this should be according to John's comment. Since you only marked it as helpful, is it still a problem?

In addition to previous entry I would suggest that you do not use Round() to convert time stamps into Dates. A date is the integer part of of a decimal value, hence you should eliminate the decimal with Floor() to not alter the Integer.

Round(3.8) = 4

Floor(3.8) = 3