Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

Using Autonumber on a key field that is already linking tables

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

1 Solution

Accepted Solutions
greend21
Creator III
Creator III
Author

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

View solution in original post

19 Replies
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
Specialist
Specialist

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
Specialist
Specialist

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

Capture.PNG

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
Specialist
Specialist

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
Creator III
Creator III
Author

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,