Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Nathan22
Contributor II
Contributor II

First sorted Value for min and max date for each ID

I am very new to qlikview and trying to get my head around the first sorted value functionality.

My problem is i have 3 columns 

PRIMARY ID , EVENTS , TIMESTAMP

now i want to find for each primary ID the event at max and min time. Primary id is getting repeated as an id has to go through the lifecycle and i have to find the stage at max time and min time in same table.

 

 

 

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Does this help you:

Data:
load [PRIMARY ID], EVENTS , TIMESTAMP inline [
PRIMARY ID, EVENTS , 	TIMESTAMP
1,			Event 1.1,	2020-02-26 14:17:01
1,			Event 1.3,	2020-02-26 16:19:03
1,			Event 1.2,	2020-02-26 15:18:02
2,			Event 2.1,	2020-02-26 17:20:04
2,			Event 2.2,	2020-02-26 18:21:05
2,			Event 2.3,	2020-02-26 19:22:06
2,			Event 2.4,	2020-02-26 20:23:07
3,			Event 3.1,	2020-02-26 21:24:08
];


Output:
LOAD  
	[PRIMARY ID],
	max(TIMESTAMP) as TIMESTAMP,
	'Last' as Stage
Resident Data
group by 	[PRIMARY ID];
LOAD  
	[PRIMARY ID],
	'First' as Stage,
	min(TIMESTAMP) as TIMESTAMP
Resident Data
group by 	[PRIMARY ID];

LEFT join
 LOAD *
 Resident Data;
Drop Table Data;
Exit script

It gives this output:

image.png

View solution in original post

2 Replies
Vegar
MVP
MVP

Does this help you:

Data:
load [PRIMARY ID], EVENTS , TIMESTAMP inline [
PRIMARY ID, EVENTS , 	TIMESTAMP
1,			Event 1.1,	2020-02-26 14:17:01
1,			Event 1.3,	2020-02-26 16:19:03
1,			Event 1.2,	2020-02-26 15:18:02
2,			Event 2.1,	2020-02-26 17:20:04
2,			Event 2.2,	2020-02-26 18:21:05
2,			Event 2.3,	2020-02-26 19:22:06
2,			Event 2.4,	2020-02-26 20:23:07
3,			Event 3.1,	2020-02-26 21:24:08
];


Output:
LOAD  
	[PRIMARY ID],
	max(TIMESTAMP) as TIMESTAMP,
	'Last' as Stage
Resident Data
group by 	[PRIMARY ID];
LOAD  
	[PRIMARY ID],
	'First' as Stage,
	min(TIMESTAMP) as TIMESTAMP
Resident Data
group by 	[PRIMARY ID];

LEFT join
 LOAD *
 Resident Data;
Drop Table Data;
Exit script

It gives this output:

image.png

Nathan22
Contributor II
Contributor II
Author

left joins really do wonders thanks for the help