Finding previous Year's Amount using a field in Single Fact table

[prathipsrinivas]

Hi Qlik Experts,

I have a single fact table, where I have fields like 

Policy,

RenewedfromPolicy,

Amount.

Here, If  RenewedfromPolicy(Old Policy) which is last year Policy Number,  from which Policy(new Policy) is derived.

I want to show in a  single straight table, Amount for new policy and Old policy something like below.

Policy, RenewedfromPolicy, Amount, PreviousPolicyAmount.

I have tried something like:-

Current:

load

Policy,

RenewedfromPolicy,

Amount 

from table;

left join (Current)

load 

RenewedfromPolicy,

sum(Amount) as PreviousPolicyAmount

resident Current

group by RenewedfromPolicy;

somehow it does not work out.

Could you please help out how to achieve this.

Thanks in advance,

Prathip

[tincholiver]

what is the id in those tables?

[prathipsrinivas]

I used RenewedfromPilicy as key and joined .

[sunny_talwar]

Not 100% sure, but may be one of these

Current:
LOAD Policy,
RenewedfromPolicy,
Amount 
FROM table;

Left Join (Current)
LOAD RenewedfromPolicy as Policy,
Sum(Amount) as PreviousPolicyAmount
Resident Current
Group By RenewedfromPolicy;

or

Current:
LOAD Policy,
RenewedfromPolicy,
Amount 
FROM table;

Left Join (Current)
LOAD Policy as RenewedfromPolicy,
Sum(Amount) as PreviousPolicyAmount
Resident Current
Group By RenewedfromPolicy;

[tincholiver]

Can you share some data?
I do not understand why you use the new quantity, you add it again and group it by policy again, in fact you do not need the group by since RenewedfromPolicy is the ID.

[prathipsrinivas]

Hi Sunny,

I have tried your approach but the reload stops right after the table where I added this script.

Do you think of any possible reason that makes the reload behaves like this?

Thanks,

Prathip

[sunny_talwar]

When you say stop, do you mean it errors out? What error message do you get?

[prathipsrinivas]

It's throwing 

joining/keeping

General Script error 

while I use left join, but when I use left Keep, there is no such behavior it reloads fine.

But the problem with the keep is, I'd have to consider two common fields so it creates synthetic key.

Any thoughts on this?

Thanks,

Prathip

[sunny_talwar]

Can you share the logfile to see this?

[prathipsrinivas]

Please find below the log:-

tmp_Doc:
2019-02-27 17:54:45 0071 Load
2019-02-27 17:54:45 0072 EntryDate as BookedDate
2019-02-27 17:54:45 0073 ,BrokerageReportingDate as EarnedDate
2019-02-27 17:54:45 0074 ,InceptionDate as RiskStartDate
2019-02-27 17:54:45 0075 ,if(InceptionDate>EntryDate,InceptionDate,if(EntryDate>=InceptionDate,EntryDate)) as RemitDate
2019-02-27 17:54:45 0076 ,TransactionBKey as [Doc Ref]
2019-02-27 17:54:45 0077 ,GrossWrittenPremium_GBP as [Gross Premium GBP]
2019-02-27 17:54:45 0078 ,RetainedRevenue_GBP as [Retained Income GBP]
2019-02-27 17:54:45 0079
2019-02-27 17:54:45 0080 ,ThirdPartyDeduction_GBP as [Third Party GBP]
2019-02-27 17:54:45 0081 ,Commission_GBP as [Commission GBP]
2019-02-27 17:54:45 0082 ,AdminFee_GBP as [Admin Fee GBP]
2019-02-27 17:54:45 0083 ,ClientDiscount_GBP as [Client Discount GBP]
2019-02-27 17:54:45 0084 ,ClientFee_GBP as [Client Fee GBP]
2019-02-27 17:54:45 0085 ,Commission_SettCcy as [Commission SettCcy]
2019-02-27 17:54:45 0086 ,AdminFee_SettCcy as [Admin Fee SettCcy]
2019-02-27 17:54:45 0087 ,ClientDiscount_SettCcy as [Client Discount SettCcy]
2019-02-27 17:54:45 0088 ,ClientFee_SettCcy as [Client Fee SettCcy]
2019-02-27 17:54:45 0089 ,GrossWrittenPremium_SettCcy as [Gross Premium SettCcy]
2019-02-27 17:54:45 0090 ,RetainedRevenue_SettCcy as [Retained Income SettCcy]
2019-02-27 17:54:45 0091
2019-02-27 17:54:45 0092 ,ThirdPartyDeduction_SettCcy as [Third Party SettCcy]
2019-02-27 17:54:45 0093 ,%DateKey
2019-02-27 17:54:45 0094 ,[PolicySectionSKey]
2019-02-27 17:54:45 0095 ,[MarketSKey]
2019-02-27 17:54:45 0096 ,[ClientSKey]
2019-02-27 17:54:45 0097 ,[AssuredSKey]
2019-02-27 17:54:45 0098 ,[ReassuredSKey]
2019-02-27 17:54:45 0099 ,[IndustrySKey]
2019-02-27 17:54:45 0100 ,[ProductSKey]
2019-02-27 17:54:45 0101 ,[BusinessUnitSKey]
2019-02-27 17:54:45 0102 ,SettlementCurrencySKey
2019-02-27 17:54:45 0103 ,DatasourceSKey
2019-02-27 17:54:45 0104 ,[RateType]
2019-02-27 17:54:45 0105 ,RiskReference
2019-02-27 17:54:45 0106 ,TransactionTypeSKey
2019-02-27 17:54:45 0107 ,ClientBKey
2019-02-27 17:54:45 0108 ,TransactionBKey
2019-02-27 17:54:45 0109 ,[Client Name]
2019-02-27 17:54:45 0110 ,[Insured Name]
2019-02-27 17:54:45 0111 ,PolicySKey
2019-02-27 17:54:45 0112 ,RenewedFromPolicySKey
2019-02-27 17:54:45 0113 ,RenewedFromRiskReference
2019-02-27 17:54:45 0114 ,PolicyExpiryDate
2019-02-27 17:54:45 0115 ,PolicyInceptionDate
2019-02-27 17:54:45 0116 ,date([PolicyExpiryDate])-date([PolicyInceptionDate]) as No.ofPolicyDays
2019-02-27 17:54:45 0117
2019-02-27 17:54:45 0118
2019-02-27 17:54:45 0119
2019-02-27 17:54:45 0120
2019-02-27 17:54:45 0121 resident Doc
2019-02-27 17:56:30 43 fields found: BookedDate, EarnedDate, RiskStartDate, RemitDate, Doc Ref, Gross Premium GBP, Retained Income GBP, Third Party GBP, Commission GBP, Admin Fee GBP, Client Discount GBP, Client Fee GBP, Commission SettCcy, Admin Fee SettCcy, Client Discount SettCcy, Client Fee SettCcy, Gross Premium SettCcy, Retained Income SettCcy, Third Party SettCcy, %DateKey, PolicySectionSKey, MarketSKey, ClientSKey, AssuredSKey, ReassuredSKey, IndustrySKey, ProductSKey, BusinessUnitSKey, SettlementCurrencySKey, DatasourceSKey, RateType, RiskReference, TransactionTypeSKey, ClientBKey, TransactionBKey, Client Name, Insured Name, PolicySKey, RenewedFromPolicySKey, RenewedFromRiskReference, PolicyExpiryDate, PolicyInceptionDate, No.ofPolicyDays,
2019-02-27 17:56:30 31,173,742 lines fetched
2019-02-27 17:56:30 0129 left join(tmp_Doc)
2019-02-27 17:56:30 0130 Load
2019-02-27 17:56:30 0131
2019-02-27 17:56:30 0132
2019-02-27 17:56:30 0133
2019-02-27 17:56:30 0134 RateType,
2019-02-27 17:56:30 0135 RiskReference as RenewedFromRiskReference
2019-02-27 17:56:30 0136
2019-02-27 17:56:30 0137 ,[Gross Premium GBP] as PriviousGrossWrittenPremium_GBP
2019-02-27 17:56:30 0138 ,[Retained Income GBP] as [PriviousRetainedRevenue_GBP]
2019-02-27 17:56:30 0139 ,[Gross Premium SettCcy] as PriviousGrossWrittenPremium_settCcy
2019-02-27 17:56:30 0140 ,[Retained Income SettCcy] as PriviousRetainedRevenue_SettCcy
2019-02-27 17:56:30 0141
2019-02-27 17:56:30 0142
2019-02-27 17:56:30 0143
2019-02-27 17:56:30 0144 resident tmp_Doc where RenewedFromPolicySKey <> -1
2019-02-27 17:56:55 6 fields found: RateType, RenewedFromRiskReference, PriviousGrossWrittenPremium_GBP, PriviousRetainedRevenue_GBP, PriviousGrossWrittenPremium_settCcy, PriviousRetainedRevenue_SettCcy,
2019-02-27 17:56:55 13,234,807 lines fetched
2019-02-27 17:56:55 Joining/Keeping
2019-02-27 18:05:28 General Script Error
2019-02-27 18:05:53 Execution Failed
2019-02-27 18:05:53 Execution finished.