topic Re: Using Autonumber on a key field that is already linking tables in QlikView

Using Autonumber on a key field that is already linking tables

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

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

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Wed, 27 Dec 2017 22:32:21 GMT

Something like this

InforceCounts:
LOAD BillSysCd,
InFClientName,
AutoNumber(InFPolicyNumber, 'Key') 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 InFPolicyNumber,
IFDate
From $(vQVDPath)IFReferenceTable.qvd (qvd);

InForceIF:
Load AutoNumber(InFPolicyNumber, 'Key') as 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

Update: Added a missing single quote above

Re: Using Autonumber on a key field that is already linking tables

luismadriz — Wed, 27 Dec 2017 23:05:14 GMT

Hi Sunny, what is that 'Key?

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Wed, 27 Dec 2017 23:37:22 GMT

Key is just an ID used to make sure that if there are more than one AutoNumber used in the script, this one is starts from 1 and not a continuation of the other AutoNumber. Always a good idea to specify a new id to make sure that the AutoNumber do not become unnecessarily high.

Re: Using Autonumber on a key field that is already linking tables

luismadriz — Wed, 27 Dec 2017 23:51:12 GMT

Ok thanks, that way you're ensuring that each distinct value of InFPolicyNumber will get the same autonumber regardless of doing it more than once on different tables.

Many thanks,

Cheers

Luis

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Thu, 28 Dec 2017 00:29:24 GMT

That is not the reason. Even AutoNumber(InFPolicyNumber) will assign a distinct value to InFPolicyNumber. I will try to create a sample for you to show what I mean.

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Thu, 28 Dec 2017 00:36:33 GMT

Try this script where I have two unlinked tables

Table1:

LOAD AutoNumber(Field1) as FirstAutoNumber,

Field1;

LOAD * Inline [

Field1

ABC

dsh

DEF

efsl

];

Table2:

LOAD AutoNumber(Field2) as SecondAutoNumber,

AutoNumber(Field2, 'Key') as ThirdAutoNumber,

Field2;

LOAD * Inline [

Field2

DEF

,ka

ehd

skd

];

and there Output is as follows

Note how SecondAutoNumber is continuing from where it left for the FirstAutoNumber, but the ThirdAutoNumber restarted from 1 because it has a unique key which tells it to restart and only continue when the same Key is used elsewhere

Re: Using Autonumber on a key field that is already linking tables

luismadriz — Thu, 28 Dec 2017 01:29:37 GMT

Thanks for this Sunny,

I got the idea of restarting the autonumber using like a different seed or reference.

What I didn't know and sort of impresses me is that I never thought that SecondAutoNumber for DEF was going to be 3 as it's a different field than Field1.... but it seems Qlik keeps that value in memory and assigns the same autonumber -assuming no or same reference-

Nice,

Thanks again,

Cheers

Luis

Re: Using Autonumber on a key field that is already linking tables

greend21 — Thu, 28 Dec 2017 13:33:20 GMT

This doesn't seem to work in my scenario. Each Policy number is repeated multiple times in the data. You helped me get it so if policy ABC123 is effective 1/1/17-1/1/18 the data would show an in effect month. So I have ABC123 for 1/31/17, 2/28/17, 3/31/17, 4/30/17, etc. It seems autonumber just takes all instances of ABC123 and gives it a 1. Is there a way for Qlikview to number these 1,2,3,4, etc? Could the key field be used for that? If not, it is not a big deal. I just figured I would build in memory savings as I go.

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Thu, 28 Dec 2017 13:35:32 GMT

Just add Effective to the AutoNumberFields

AutoNumber(InFPolicyNumber&Effective, 'Key') as InFPolicyNumber,

Re: Using Autonumber on a key field that is already linking tables

greend21 — Thu, 28 Dec 2017 14:17:43 GMT

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

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Thu, 28 Dec 2017 14:20:00 GMT

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.

Re: Using Autonumber on a key field that is already linking tables

greend21 — Thu, 28 Dec 2017 14:32:20 GMT

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)

Re: Using Autonumber on a key field that is already linking tables

sunny_talwar — Thu, 28 Dec 2017 14:35:59 GMT

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.

Re: Using Autonumber on a key field that is already linking tables

Peter_Cammaert — Thu, 28 Dec 2017 14:50:24 GMT

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.

Re: Using Autonumber on a key field that is already linking tables

greend21 — Thu, 28 Dec 2017 16:17:26 GMT

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.

Re: Using Autonumber on a key field that is already linking tables

greend21 — Thu, 28 Dec 2017 16:23:20 GMT

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.

Re: Using Autonumber on a key field that is already linking tables

Peter_Cammaert — Thu, 28 Dec 2017 16:50:30 GMT

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.

Re: Using Autonumber on a key field that is already linking tables

greend21 — Thu, 28 Dec 2017 18:25:20 GMT

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?

Re: Using Autonumber on a key field that is already linking tables

greend21 — Fri, 29 Dec 2017 19:39:14 GMT

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