Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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