Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

2 Replies
Vegar
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Nathan22
Contributor II
Contributor II
Author

left joins really do wonders thanks for the help