topic Filter only last version of historical data in App Development

Filter only last version of historical data

_Julien_ — Thu, 19 Aug 2021 14:15:25 GMT

Dear all

I'm quite new in the community, and I have a problem for which I've not found any answer.

I use QlikSense Cloud App for dashboarding data from a CRM. Every day, the data is extracted from the CRM and updated.

Hence, I may have multiple rows for each business lead, each time it is updated.

For instance, here is an exemple :

ID | STATUS | UPDATE_DATE | VALUE

1 | qualification | 2021/08/19 | 10000

2 | qualification | 2021/08/19 | 5000

1 | forecast. | 2021/08/03 | 2000

3 | forecast. | 2021/07/23 | 8000

My need is to have

1/ A table with SUM(VALUE) for each STATUS at the current date (now). hence if we are August, 19th

qualification --> 10000 + 5000 = 15000

forecast --> 8000 (do not take into account the 2000 line, as it has already been taken into account in qualification)

2/ A table with SUM(VALUE) for each STATUS at any date, as I could want to have a historical view of my CRM. Of course, new data updates should then be ignored.

I tried different versions of FIRSTSORTEDVALUE and AGGR, but nothing worked.

One thing is the we cannot "think" only at the STATUS level, as otherwise my ID 1 lead above would be counted twice. It seems I would need a global filter selecting only the "last" version of each row, and a ability to set a date for the "last" parameter.

What do you think ?

Many thanks.

Re: Filter only last version of historical data

Vegar — Thu, 19 Aug 2021 16:49:30 GMT

You could solve this with interval match. Take a look at this script.

SET DateFormat='YYYY/MM/DD';
RAW:
LOAD * inline [
ID, STATUS, UPDATE_DATE, VALUE
1, qualification, 2021/08/18, 10000
2, qualification, 2021/08/18, 5000
1, forecast., 2021/08/03, 2000
3, forecast., 2021/07/23, 8000
]
;
Data:
LOAD ID, STATUS, UPDATE_DATE as Date_from,VALUE, if(ID=Peek(ID),DayEnd( Peek(Date_from),-1), dayend(today())) as Date_to
Resident RAW
ORDER BY ID, UPDATE_DATE desc
;

LOAD
dayname(date#('2021/07/22')+RecNo()) as Date
AutoGenerate today() - date#('2021/07/22');

IntervalMatch(Date)
LOAD Date_from,Date_to
Resident Data;

DROP TABLE RAW;

Re: Filter only last version of historical data

_Julien_ — Fri, 20 Aug 2021 15:32:57 GMT

Hi Vegar

Thanks for your answer. I get inspiration from what you did, and here is what I finally did.

My data comes from "pipedrive", our CRM. So in the data load editor,

1/ I loaded the deals table, with only the fields I need : id, add_time (that is creation time) and update_time.

2/ I created one temporary table, 'deals_from_one_month_ago'. The only things that is done is to filter on "add_time", and to retrieve the last available version of the row, through the ID=Peek(ID) trick that you showed above.

3/ Now in my sheet, thanks to the magic of QlikSense that automatically handle foreign keys, I can select the relevant data based on the value on the two "TIMETRAVEL_ONEMONTHAGO" field.

Here is the script :

LIB CONNECT TO [Google_BigQuery_acme-datawarehouse]; [deals_tempo]: LOAD [id] [update_time] [add_time] SELECT id, `update_time`, `add_time`, FROM `pipedrive_acme.deals`; [deals_from_one_month_ago]: LOAD [id],[update_time], if([id]=Peek([id]),'NO', 'YES') as 'TIMETRAVEL_ONEMONTHAGO' Resident [deals_tempo] WHERE [add_time] <= date(Today()-30) ORDER BY [id], [update_time] desc ; DROP TABLE [deals_tempo];

Of course I can create more tables to handle 2 months ago, now, and so on.

Julien