Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
varmekontrol
Creator
Creator

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. 

image.png

 

 

 
Labels (3)
1 Solution

Accepted Solutions
Taoufiq_Zarra

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

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
varmekontrol
Creator
Creator
Author

 

 

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

 

Taoufiq_Zarra

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

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
varmekontrol
Creator
Creator
Author

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

Thank you for your help.