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

[sunny_talwar]

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

[luismadriz]

Hi Sunny, what is that 'Key?

[sunny_talwar]

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.

[luismadriz]

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

[sunny_talwar]

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.

[sunny_talwar]

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

[luismadriz]

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

[greend21]

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.

[sunny_talwar]

Just add Effective to the AutoNumberFields

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