Filter only last version of historical data

[_Julien_]

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.

[Vegar]

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;

[_Julien_ · Accepted solution]

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