Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

 

 

 
1 Solution

Accepted Solutions
Highlighted
Master
Master

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

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

View solution in original post

4 Replies
Highlighted
Master
Master

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

@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 "
Highlighted
Creator
Creator

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

 

 

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

 

Highlighted
Master
Master

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

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

View solution in original post

Highlighted
Creator
Creator

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

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

Thank you for your help.