Loop through field and assign a number

[robin_heijt]

Hi,

I am using this piece of code in the data load:

Movements:

LOAD
    PERNR,
    Lname,
    Fname,
    Mname,
    "EE Group",
    "EE Subgroup",
    "Action Begda",
    "Action type",
    "Action Reason",
    F10,
    "Band Before",
    "Position Before",
    "Position Name Before RU .",
    "Position Name Before EN",
    "Legacy Position ID Before",
    "Legacy Dept ID Before",
    "Legacy Cat Code Before",
    "Cost Center Before",
    "Function Before",
    "Band After",
    "Position After",
    "Position Name After RU",
    "Position Name After EN",
    "Legacy Position ID After",
    "Legacy Dept ID After",
    "Legacy Cat Code After",
    "Cost Center After",
    "Function After"
FROM [***]
(ooxml, embedded labels, table is Sheet1);







FOR Each a in FieldValueList('PERNR')

Movement#:

let GlobalID = a;

LOAD '$(GlobalID)'  as "PERNR" AutoGenerate 1;






NEXT a

This script will loop perfectly through all the PERNR and assign it as a variable.

Now I would like to generate a new collumn that will assign a random number or sequential number to each PERNR.

E.g. the list of PERNR =

100

200

300

400

I want a new Column

100=1

200=2

300=3

400=4

This is example data and will have to go through a loop.

Thanks.

[robin_heijt]

Hi Rob,

Thank you so much. This works great.

As a last question, I was wondering if you would be able to tell me if it is possible to add the people without a movement to this cycle?

So right now when I select a cycle, I will only see people that have a movement sequence. However I also have people without a movement("Action Begda"). I would like to see these people too when I select the cycle.

Aswell that I have people with a move in the future e.g. 31/12/2018. When i select the cycle for 2018/2019, these people show up to. i would only like to show until the current date.

Thank you so much.

[rwunderlich]

Since "Action Bega" defines a cycle, I'm not sure how you would associate people to a cycle that don't have an "Action Bega".

-Rob

[robin_heijt]

Hi Rob,

I have another question.

As I applied the "Where" Adherence filter. I noticed that my entire population gets filtered based on this Adherence filter.

However I only need the autonumber sequence based on this filter and not the full database. Is there a way to do this?

 //  
 NoConcatenate
    Load 
    "Personal ID Movement OPR",
        "Personal ID Movement",
        "Name Movement",
    "Employee Group Movement",
        "Effective Date",
        "Action type",
        "Action Reason",
        "Band Before",
        "Position ID Before",
        "Position Before",
        "Function Before",
        "Band After",
        "Position ID After",
    "Position After",
        "Function After",
           "OPR 2018 Move",
      "OPR 2015 Move",
      "OPR 2016 Move",
      "OPR 2017 Move"

        Resident Movement;
        Drop Table Movement;


 Sequence:     
 LOAD *,

      AutoNumber("Effective Date", "Personal ID Movement OPR" & Cycle) as "Movement Number"

 RESIDENT Movement_OPR
 where "Adherence Filter" = 'Adherence'
 Order by "Effective Date";
Drop Table Movement_OPR

This is the code right now.

[rwunderlich]

Instead of recreating the table, join the autonumber field to the original table.

Join (Movement_OPR)

LOAD *,   

      AutoNumber("Effective Date", "Personal ID Movement OPR" & Cycle) as "Movement Number"   

RESIDENT Movement_OPR 

where "Adherence Filter" = 'Adherence' 

Order by "Effective Date"; 

-Rob