Age Calculation

[hitha1512]

Hi Team,

I have a requirement to calculate the Age. 

Requirement - 

Compare field "Agreement Name" with last business days "Agreement Name" data. If new, then = 0, If was in the previous report, then +1 to the existing Days OLD count. If the agreement name drops off the report and appears the next day the count would restart from 0 again.

attaching raw data files and the results required.

Note : we receive daily files and there can be cases where we might not receive files 

thanks for your help in advance.

Regards,

Hitha Dhani

[sujana1621]

Hi Hitha,

Can you provide with the sample data.

Thanks

Sujana

[hitha1512]

Hey thank you for responding. have attached the files. 🙂

[tresesco]

Hi Hitha,

Are you looking for a script solution or UI? What is your data load strategy?

[hitha1512]

Hi I am looking for script resolution. 

its just one single table(one file load per day).... with multiple fields. I have provided just 2 fields required for calculation.

[tresesco]

If you keep concatenating the table daily, it looks that by only counting your country - you could the expected result. Let me know if I am missing anything.

[hitha1512]

Hi , 

I don't think that would work. Giving examples below 

> Afghanistan : Considering today to be 23rd, we will have to compare data to precious day 22nd. if present, Age has to be 1.

when we receive data on 24th, we will have to compare data to 23rd. if present Age will be +1 to the existing age which would be 1+1 =2.

So the Age for Afghanistan on 24th will be 2. the age will keep incrementing by +1 

This increment will stop or come back to 0 when it does not find the record the previous day or same day

if on 25th we do not get Afghanistan. it drops off the record. if comes back on 26th, age would be 0. if comes back on 27th again, age will be 1.

I hope this is not confusing.

[tresesco]

Got it. However, would require a bit more explanation in load strategy. Are you using qvd to load the historical data or partial reload?

[hitha1512]

Currently loading all files and saving that into QVD. I have to make just this calculation for the report.

all the fields are direct from file.

[tresesco · Accepted solution]

Try like:

temp:
LOAD valuation_date, 
     agreement_name
FROM
[Exposures_20191122.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD valuation_date, 
     agreement_name
FROM
[Exposures_20191123.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD valuation_date, 
     agreement_name
FROM
[Exposures_20191124.xlsx]
(ooxml, embedded labels, table is Sheet1);

t1:
	 
LOAD 
	*,
	If(peek(agreement_name)=agreement_name and Floor(valuation_date-Peek(valuation_date))=1, RangeSum(peek(Age),1),0) as Age
Resident	temp order by agreement_name,valuation_date;

Drop table temp;
Load
	Max(valuation_date) as MaxDate
Resident t1;
	
Let vMaxDate=Date(Peek('MaxDate'));

t2:
Load
	agreement_name,
	Age
Resident t1 where 	valuation_date='$(vMaxDate)';
Drop Table t1;