topic Link Time Periods Table in QlikView

Link Time Periods Table

Thu, 12 May 2011 10:35:21 GMT

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

Link Time Periods Table

stephencredmond — Thu, 12 May 2011 11:56:01 GMT

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

Link Time Periods Table

ToniKautto — Thu, 12 May 2011 12:52:12 GMT

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

Link Time Periods Table

Thu, 12 May 2011 15:46:37 GMT

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

Re: Link Time Periods Table

johnw — Thu, 12 May 2011 23:06:10 GMT

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.

Link Time Periods Table

ToniKautto — Fri, 13 May 2011 11:27:14 GMT

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