Discussion Board for collaboration related to QlikView App Development.
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
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
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
Hi Sunny, what is that 'Key?
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.
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
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.
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
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
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.
Just add Effective to the AutoNumberFields
AutoNumber(InFPolicyNumber&Effective, 'Key') as InFPolicyNumber,