- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load.
So I think I have studied all subjects I could find on this topic, and the closest I got to a solution was this answer from the always helpful Sunny_talwar -> https://community.qlik.com/t5/New-to-Qlik-Sense/Firstsortedvalue-not-working-in-Load-Script-on-QlikS...
Here I found out that all dimensions needed to be in the Group By statement.
This is what I am trying to do:
Only load the lates records, determined by the period_end
Property_list:
LOAD
admin_id,
property_no,
property_name,
FirstSortedValue(works_id, -period_end) as works_id,
period_end
FROM
[C:\Temp\File_$(vFileName)]
(txt, utf8, embedded labels, delimiter is ';', msq) Group By admin_id, property_no, property_name, works_id, period_end;
This is my result, where the records for multiple period _end still exists.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think there's something missing 🙂 but based to what u share, one solution :
Data:
LOAD * INLINE [
admin_id, property_no,property_name, period_end
251359, 7376345039, Property_1, 31-05-2020
251324, 20964, Prop, 31-05-2019
251359, 7376345039, Property_1, 31-05-2019
251359, 7376345039, Property_1, 31-05-2019
251359, 7376345039, Property_1, 31-05-2018
2515, 4047306875, Prop, 31-05-2020
2514, 12132397, Prop, 31-05-2020
25111, 1402539578, Prop, 31-05-2019
25123, 4047306875, Prop, 31-05-2021
];
Property_list:
noconcatenate
load admin_id,FirstSortedValue(period_end,-Date#(period_end,'DD-MM-YYYY')) as period_end,FirstSortedValue(property_no,-Date#(period_end,'DD-MM-YYYY')) as property_no,FirstSortedValue(property_name,-Date#(period_end,'DD-MM-YYYY')) as property_name
resident Data Group by admin_id;
drop table Data;
output :
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@varmekontrol here you have grouped by all dimensions, that's why the output will keep all rows Max of a row is a row, if I understood well you need :
Property_list:
LOAD
admin_id,
property_no,
property_name,
FirstSortedValue(works_id, -period_end) as works_id,
period_end
FROM
[C:\Temp\File_$(vFileName)]
(txt, utf8, embedded labels, delimiter is ';', msq) Group By admin_id, property_no, property_name;
otherwise you can share a sample data with the output to propose something else.
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
LOAD * INLINE [
admin_id, property_no, property_name, works_id, period_end
251359, 7376345039, Property_1, 555334, 31-05-2020
251324, 20964, Prop, 232323, 31-05-2019
251359, 7376345039, Property_1, 555334, 31-05-2019
251359, 7376345039, Property_1, 555334, 31-05-2019
251359, 7376345039, Property_1, 555334, 31-05-2018
2515, 4047306875, Prop, 1323, 31-05-2020
2514, 12132397, Prop, 23324, 31-05-2020
25111, 1402539578, Prop, 44456, 31-05-2019
25123, 4047306875, Prop, 2134234, 31-05-2021
];
Property_list:
LOAD
admin_id,
property_no,
property_name,
FirstSortedValue(works_id, -period_end) as works_id,
period_end
FROM
[C:\Temp\File_$(vFileName)]
(txt, utf8, embedded labels, delimiter is ';', msq) Group By admin_id, property_no, property_name;
Here is some data to help you, sorry for the lack of it.
Here you can see that the admin_id 251359 is present multiple times, because of different dates in period_end
I would like to only keep one record, that is the record were the latest period_end is 31-05-2020
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think there's something missing 🙂 but based to what u share, one solution :
Data:
LOAD * INLINE [
admin_id, property_no,property_name, period_end
251359, 7376345039, Property_1, 31-05-2020
251324, 20964, Prop, 31-05-2019
251359, 7376345039, Property_1, 31-05-2019
251359, 7376345039, Property_1, 31-05-2019
251359, 7376345039, Property_1, 31-05-2018
2515, 4047306875, Prop, 31-05-2020
2514, 12132397, Prop, 31-05-2020
25111, 1402539578, Prop, 31-05-2019
25123, 4047306875, Prop, 31-05-2021
];
Property_list:
noconcatenate
load admin_id,FirstSortedValue(period_end,-Date#(period_end,'DD-MM-YYYY')) as period_end,FirstSortedValue(property_no,-Date#(period_end,'DD-MM-YYYY')) as property_no,FirstSortedValue(property_name,-Date#(period_end,'DD-MM-YYYY')) as property_name
resident Data Group by admin_id;
drop table Data;
output :
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That did the trick, I just need to do it on all of the dimensions.
Thank you for your help.