Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi Hitha,
Can you provide with the sample data.
Thanks
Sujana
Hey thank you for responding. have attached the files. 🙂
Hi Hitha,
Are you looking for a script solution or UI? What is your data load strategy?
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.
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.
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.
Got it. However, would require a bit more explanation in load strategy. Are you using qvd to load the historical data or partial reload?
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.
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;