topic Re: FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load. in QlikView

FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load.

varmekontrol — Sat, 16 Nov 2024 00:29:32 GMT

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-QlikSense/td-p/79065
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.

Re: FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load.

Taoufiq_Zarra — Fri, 12 Jun 2020 09:54:20 GMT

@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 :

otherwise you can share a sample data with the output to propose something else.

Re: FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load.

varmekontrol — Fri, 12 Jun 2020 12:13:47 GMT

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

Re: FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load.

Taoufiq_Zarra — Fri, 12 Jun 2020 12:27:37 GMT

@varmekontrol

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 :

Re: FirstSortedValue with date, and multiple dimensions. GIve unknown error in script - load.

varmekontrol — Mon, 15 Jun 2020 11:51:45 GMT

That did the trick, I just need to do it on all of the dimensions.

Thank you for your help.