Using Autonumber on a key field that is already linking tables

[greend21]

The document analyzer is saying I could free up about 1MB of space by using Autonumber() on one of my field InFPolicyNumber. Would I need to Autonumber both? Would they still join correctly? I tried reading a bunch of material on the subject but nothing gave a clear answer.

InforceCounts:
LOAD BillSysCd,
InFClientName,
InFPolicyNumber,
InFFarmFlag as [~InFFarmFlag],
InFELSFlag as [~InFELSFlag],
MCD as MCDInF
// PolEffDt,
// Month(PolEffDt) as PolEffDtMonth,
// PolExpDt
FROM $(vQVDPath)InforceCounts.qvd (qvd);

Left Join(InforceCounts)
Load InFPolicyNumber,
IFDate
From $(vQVDPath)IFReferenceTable.qvd (qvd);

InForceIF:
Load InFPolicyNumber,
// IFDate as InFDate,
  MakeDate(Year(IFDate), Month(IFDate)) as InFMoYr,
// Month(IFDate) as InFMonth,
// Year(IFDate) as InFYear,
  If((IFDate>=AddMonths(Monthstart(Today()),-13)) and (IFDate<=MonthEnd(Today())),1,0) as InFRoll13Flag
Resident InforceCounts

[greend21]

I used IFDate since that is the field that has the end of month date for each effective month. The issue looks like when I do that IFDate is only in the second table so the data will never join because InFPolicy number in table one would be ABC123 and InFpolicy Number from table two would be ABC123&1/31/17

[sunny_talwar]

So why do you want to add IFDate to one of the tables? What is wrong with repeating number for a single InFPolicy? I don't think I fully understand your issue.

[greend21]

IFDate is end of each month that a policy is effective that way I can get a count of effective policies for each month. Without that I only have effective and expiration dates and no way to get a count for the months in between. Adding the IFDate makes the data look like this (along with various other fields):

Policy ABC123 effective 1/1/17-1/1/18 and DEF456 effective 5/1/17-5/1/18.

Policy, IFDate

ABC123 , 1/31/17

ABC123, 2/28/17

ABC123, 3/31/17(all the way to 12/31/17)

DEF456, 5/31/17

DEF456, 6/30/17

DEF456, 7/31/17 (all the way to 4/30/18)

[sunny_talwar]

I am going to take a step back and ask you this... Your initial data model was working, right?

The only think you really wanted to do was to free up some space using AutoNumber, right again hopefully? So, where did the effective date came in from? If you wish to keep all things like previous (the model which worked), you don't want to include IFDate in the AutoNumber.

[Peter_Cammaert]

AFAIK you're trying to use one field for two purposes, creating sort of a split personality. That will not work. For example, if you let AutoNumber() number all occurrences of PolicyID differently, you'll get into trouble again when adding a table of policy details without repeating each entry for all months a policy is effective.

Keys serve a single purpose: stitching your data model together. Aggregation should be done on other fields, so as to prevent any side-effects of key values occurring multiple times in different table columns carrying the same field name. Of course, you can avoid those using other techniques, but as a general practice, better separate keys from data fields.

[greend21]

Correct. The effective date was used to calculate the IFDate. The IFDate lets me know if the policy is In Force in a given month. If I only do as your first comment suggests then my pivot table is blank.

[greend21]

So then what I am trying to do is not possible? I would need the InFPolicyNumber to have a different AutoNumber for each IFDate. For example, every row here is actually the same policy number. Autonumber seems to condense all these to one number. Because of the different IFDate I need a number for each.

[Peter_Cammaert]

Why would you need a different number for each month a policy is in effect? And - just like Sunny was wondering - is it working now without the AutoNumber() modification? Then it should work with the AutoNumber() modification just like it did before.

Imagine I have a bar chart with a single dimension IFDate (as a substitute for a MonthYear value), and count(InFPolicyNumber) as an expression. Whether InFPolicyNumber is ABC123 or 1, the count will produce the same result and the bar chart will look the same.

Ok, maybe we're running ahead of ourselves and creating problems in the link between the table on the left and the table on the right. But not really, since you seem to be creating the InForceIF table from the InforceCounts table. That means that if you AutoNumber() whatever key value you have in the table on the left, it will get copied to the table on the right as-is. The link field would stay functional. And that's ok, because you're not using the key field for anything else than linking the two tables. Even if you change the Bar chart to use InFMoYr as a dimension, the display would still be the same.

It's just that in the latter case and using AutoNumber() to change the content of the key field to a sequence number, I would add an original Policy number in a new field in the table on the left. Thereby separating keys from fields that are used in aggregation expressions.

[greend21]

Yes, everything works fine without the autonumber modification but there is some lag. It was my understanding that using autonumber could free up some RAM and maybe reduce lag. Adding autonumber shows a blank pivot and actually shows null values for the IFDate in the table viewer, so each number is shown once. If IFDate didn't change to null it would probably be fine. I would think this is because the left join doesn't find matching InFPolicyNumbers in the second table since table one would shoud 1 but table two would show ABC123?

If I have an original policy number field doesn't that defeat the purpose of using autonumber because all the policy numbers are still in the table, therefore not saving any memory?

[greend21 · Accepted solution]

This works but according to document analyzer and the compare tool it made no difference.

InforceCounts:
LOAD BillSysCd,
InFClientName,
Autonumber(InFPolicyNumber,'InFPolicyNumber') as InFPolicyNumber,
InFFarmFlag as [~InFFarmFlag],
InFELSFlag as [~InFELSFlag],
MCD as MCDInF
// PolEffDt,
// Month(PolEffDt) as PolEffDtMonth,
// PolExpDt
FROM $(vQVDPath)InforceCounts.qvd (qvd);

Left Join(InforceCounts)
Load Autonumber(InFPolicyNumber,'InFPolicyNumber') as InFPolicyNumber,
IFDate
From $(vQVDPath)IFReferenceTable.qvd (qvd);

InForceIF:
Load InFPolicyNumber,
// IFDate as InFDate,
  MakeDate(Year(IFDate), Month(IFDate)) as InFMoYr,
// Month(IFDate) as InFMonth,
// Year(IFDate) as InFYear,
  If((IFDate>=AddMonths(Monthstart(Today()),-13)) and (IFDate<=MonthEnd(Today())),1,0) as InFRoll13Flag
Resident InforceCounts