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

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

prathipsrinivas — Sat, 16 Nov 2024 21:20:40 GMT

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

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

tincholiver — Tue, 26 Feb 2019 19:21:17 GMT

what is the id in those tables?

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

prathipsrinivas — Tue, 26 Feb 2019 20:23:55 GMT

I used RenewedfromPilicy as key and joined .

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

sunny_talwar — Tue, 26 Feb 2019 20:36:08 GMT

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;

Current:
LOAD Policy,
RenewedfromPolicy,
Amount 
FROM table;

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

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

tincholiver — Tue, 26 Feb 2019 20:36:16 GMT

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.

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

prathipsrinivas — Wed, 27 Feb 2019 09:27:14 GMT

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

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

sunny_talwar — Wed, 27 Feb 2019 12:06:31 GMT

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

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

prathipsrinivas — Wed, 27 Feb 2019 13:28:12 GMT

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

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

sunny_talwar — Wed, 27 Feb 2019 13:40:13 GMT

Can you share the logfile to see this?

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

prathipsrinivas — Thu, 28 Feb 2019 08:55:39 GMT

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.

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

prathipsrinivas — Thu, 28 Feb 2019 11:35:20 GMT

Hi Guys,

Just wanted to know, is it possible to handle it in the Straight table itself rather than writing this logic in the Script?

Something like this:-

RiskReference, RenewedRiskReference, Amount, Previous Amount

123, 023, 23456,78654

backend data is like this:-

RiskReference, RenewedRiskReference, Amount

123,023,23456

023,-,78654

It would be really useful if I get any suggestions here.

Thanks,

Prathip

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

sunny_talwar — Thu, 28 Feb 2019 12:55:29 GMT

Would you be able to provide a sample or some sample data to help you better here?