Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
girishchhatani1
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
olivierrobin
Specialist III
Specialist III

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

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

girishchhatani1
Contributor III
Contributor III
Author

Tried using but didn't worked.

Thanks & Regards,

Girish Chhatani

olivierrobin
Specialist III
Specialist III

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

girishchhatani1
Contributor III
Contributor III
Author

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

Thanks its working fine.

olivierrobin
Specialist III
Specialist III

glad to help you

tag this answer as correct and close the thread