Assign movement number by date

[robin_heijt]

Hi,

I am currently working with multiple data sets.

1. Headcount report (Every person listed once) based on ID and Name

2. Movement Report (Includes the same list of people, however every person has multiple entries based on their movement)

I have combined these tables, and now I need to assign movement numbers.

This used to be done through manual input by going over every single person, and based on their date of movement, I'd assign either 1, 2 or 3.

Now I would like to somehow automate this within the load script.

Is it possible in the load script to assign such a movement number based on a date field? So I can filter out duplicates in my list and filter only by first movement e.g.

Thanks

[bhaskar_sm]

Hi,

Can you provide the sample data and expected output to have clear understanding.

Regards,

Bhasker

[robin_heijt]

Sure,

Headcount:

LOAD

    "Global ID",

    "Global ID" as "Global ID Movement",

    "Personnel Number",

    Capitalize("First name"&' '& "Last name") as "Full Name",

Movement report:

LOAD

   PERNR as "Global ID Movement",

    Capitalize(Fname&' '&Lname) as "Full Name Movement",

   "Action Begda" as "Date of the Movement",

    "Action type",

    "Action Reason",

Here I linked the 2 reports by Global ID.

Resulting in this:

Based on the date of the movement I want to assign a movement number.

I hope this helps.