Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Getting previous value against a category and subcategory

Hey guys,

Got struck in one task wherein i want to fetch previous fiscal year month end value against each Finance LOB and Claim Type.

For ex:- Finance LOB = MOTOR-2W and Claim Type = OD and as mentioned in the attached excel Retension Loss for FY 2016 and Month =12 (March) is 100 and that value 100 should get applied on each month in the next fiscal year i.e FY 2017 Apr till FY 2017 Mar.

Similarly for FY 2018 Apr - FY 2018 Mar Fiscal year 2017 value of 200 should get applied in FY 2018 Apr till FY 2018 Mar.

Expected Output is also specified in the attached excel.

I tried using peek and previous function but not able to get the value against each Finance LOB and Claim Type i.e OD or TP

Thanks & advance,

Girish Chhatani

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Getting previous value against a category and subcategory

I Tried that :

t:
LOAD ClaimType,
Claim_Fiscal_Year,
Fiscal_Month_No,
FINANCE_LOB,
RETENTION_LOSS,
[Expected Output]
FROM

(
biff, embedded labels, table is Sheet_final$);

left join(t)

load ClaimType,

Claim_Fiscal_Year+1 as Claim_Fiscal_Year,

FINANCE_LOB,

RETENTION_LOSS as ExpectedOutput2

resident t

where Fiscal_Month_No=12
;
and the resilt is

that seems to be your requirement

View solution in original post

5 Replies
Highlighted
Specialist III
Specialist III

Re: Getting previous value against a category and subcategory

hello

i would use

1 - load your data

2 - left join the table on itself

left join(table)

load ClaimType,

Claim_Fiscal_Year+1 as Claim_Fiscal_Year,

FINANCE_LOB,

RETENTION_LOSS as ExpectedOutput

resident MyTable

where Fiscal_MonthNo=12

Highlighted
Contributor III
Contributor III

Re: Getting previous value against a category and subcategory

Tried using but didn't worked.

Thanks & Regards,

Girish Chhatani

Highlighted
Specialist III
Specialist III

Re: Getting previous value against a category and subcategory

I Tried that :

t:
LOAD ClaimType,
Claim_Fiscal_Year,
Fiscal_Month_No,
FINANCE_LOB,
RETENTION_LOSS,
[Expected Output]
FROM

(
biff, embedded labels, table is Sheet_final$);

left join(t)

load ClaimType,

Claim_Fiscal_Year+1 as Claim_Fiscal_Year,

FINANCE_LOB,

RETENTION_LOSS as ExpectedOutput2

resident t

where Fiscal_Month_No=12
;
and the resilt is

that seems to be your requirement

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Getting previous value against a category and subcategory

Thank you Olivier actually i had mistakenly added month no in join in the previous script.

Thanks its working fine.

Highlighted
Specialist III
Specialist III

Re: Getting previous value against a category and subcategory

glad to help you

tag this answer as correct and close the thread