topic Re: select last sigle for each user in Connectivity & Data Prep

select last sigle for each user

cedfoning — Wed, 02 Nov 2022 17:01:13 GMT

Hello,

i have an issue, need help :

i have a table as below

MAT	Affectation	affectation date start	affectation date end
62YTE	GE/PDG/IND/SM/DCS/FABCS/DEBFOR/APC	2022-09-05	2999-12-31
62YTE	GE/PDG/IND/SM/DCS/FABCS/DEBFOR/FORPLA	2022-09-01	2022-09-04
0976H	GE/PDG/IND/BS/2AP/AC/AC6	2022-09-05	2999-12-31
0976H	GE/PDG/IND/BS/2AP/AC/AC7	2022-09-01	2022-09-04
HIY67	GE/PDG/IND/SM/DEA/PME/USI1	2022-09-05	2999-12-31
HIY67	GE/PDG/IND/SM/DEA/PME/USI2	2022-09-01	2022-09-04
87ZGS	GE/PDG/IND/SM/DEA/PME/USI1	2022-09-05	2999-12-31
87ZGS	GE/PDG/IND/SM/DEA/PME/USI3	2022-09-01	2022-09-04
HUYZ9	GE/PDG/IND/EN/DEO/CI/CDIM	2022-09-05	2999-12-31
HUYZ9	GE/PDG/IND/EN/DEO/CI/CRM	2022-09-01	2022-09-04

We have users with the column MAT, that changed Affectation
The business users wants to have the latest Affection
All these infos are in the same table.

I need help please.

Re: select last sigle for each user

BrunPierre — Wed, 02 Nov 2022 18:44:49 GMT

With this front end expression

Aggr(FirstSortedValue([affectation date end],-[affectation date end]),MAT)

Re: select last sigle for each user

cedfoning — Thu, 03 Nov 2022 13:36:48 GMT

hello @BrunPierre

thanks for your anwser
i wanted to do it in the script, and i did something like this :

LOAD Mat,

FirstSortedValue([affectation date end],-[affectation date end]) as indivsmallest,

Resident Temp

Group By Mat;

But not working. it gives me Null values

Re: select last sigle for each user

BrunPierre — Thu, 03 Nov 2022 15:41:34 GMT

Hi @cedfoning, perhaps with the code below.

Temp:
LOAD MAT,
Affectation,
[affectation date start],
[affectation date end]

FROM DataSourceTable;

Data:
LOAD MAT,
Maxstring(Affectation) as Affectation,
Max([affectation date start]) as [affectation date start],
FirstSortedValue([affectation date end],-[affectation date end]) as indivsmallest

Resident Temp
Group By MAT;

DROP Table Temp;

EXIT SCRIPT;