topic Re: Age Calculation in QlikView

Age Calculation

hitha1512 — Wed, 27 Nov 2019 06:24:21 GMT

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

Re: Age Calculation

sujana1621 — Tue, 26 Nov 2019 05:58:28 GMT

Hi Hitha,

Can you provide with the sample data.

Thanks

Sujana

Re: Age Calculation

hitha1512 — Wed, 27 Nov 2019 06:24:58 GMT

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

Re: Age Calculation

tresB — Wed, 27 Nov 2019 06:49:08 GMT

Hi Hitha,

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

Re: Age Calculation

hitha1512 — Wed, 27 Nov 2019 07:07:58 GMT

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.

Re: Age Calculation

tresB — Wed, 27 Nov 2019 07:13:54 GMT

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.

Re: Age Calculation

hitha1512 — Wed, 27 Nov 2019 07:37:45 GMT

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.

Re: Age Calculation

tresB — Wed, 27 Nov 2019 07:56:18 GMT

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

Re: Age Calculation

hitha1512 — Wed, 27 Nov 2019 08:13:15 GMT

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.

Re: Age Calculation

tresB — Wed, 27 Nov 2019 09:30:45 GMT

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;

Re: Age Calculation

hitha1512 — Wed, 27 Nov 2019 10:21:18 GMT

Fabulous ! thanks a ton for your quick response 🙂 Much appreciated !

Re: Age Calculation

hitha1512 — Thu, 06 Feb 2020 09:23:04 GMT

Hi Tresesco,

the below solution worked. but now i have been asked to exclude the Public holidays and weekends. please see my explanation below.

I receive excel files daily except on weekends and public holiday. so for these days i dont have the date populated in application.

Please refer the attached screen shot. for 28/01/2020 the age expected is 1, as 27th was public holiday and 25 and 26 were weekends. I do not have 25,26,27 populated in the dashboard.

your code is checking for the previous day.

-------Floor(valuation_date-Peek(valuation_date))=1,

what i want would be based on file. if the previous file that i received has the agreement name or not.

would appreciate if you could help 🙂

Thanks & Regards,

Hitha Dhani