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.

[vamsee]

If your loop is working correctly. Try

Let i=1;

FOR Each a in FieldValueList('PERNR') 

Movement#: 

let GlobalID = a;

LOAD '$(GlobalID)'  as "PERNR",

'$(i)' as Seq_Number

AutoGenerate 1; 

Let i=i+1;

NEXT a 

[robin_heijt]

Thank you very much for your reply.

I am currently unable to test the code, but it looks like what I need. I'll give it a try as soon as possible 

However this is just the basis of my solution.

As you can see in the load script I have a field called action type and date of movement

Every PERNR has multiple entries in the excel file, all having a different action type based on a different date.

My goal is to have the loop go through all the PERNR and assign a sequential number based on the date of the movement.

So the first movement will be 1, the later date will be 2 etc...

Any chance you could advise on this situation?

Thanks.

[rwunderlich · Accepted solution]

I would generate the sequence numbers using autonumber() rather than a loop.

Sequence:

LOAD *

     AutoNumber("Action Begda", PERNR) as MovementSequence

RESIDENT Movements

Order by "Action Begda",

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[robin_heijt]

Hi Rob,

Thank you very much for your reply.

That just helped me almost to the finish!

Maybe you could help advise on how to get to the absolute finish of this situation.

Right now the sort order is being based only on the day of the movement, however the movements are based through multiple months and years. Can I somehow create a sort order based on the full date range? (dd.mm.yyyy)

The second issue is that I have a filter for this file, which filters out certain action types, currently the movement numbers are based on all the movements, but I need to have it based on the specific filter.

The action types consists of 12 different values,

4 of them are grouped under "Baseline"

the others are grouped under "Out of baseline"

Can you inform me how I can base the numbering only on the baseline filter?

I look forward to hear from you.

Thanks.

[rwunderlich]

"Can I somehow create a sort order based on the full date range? (dd.mm.yyyy)"

I assume you have a field that represents this dd.mm.yyyy value?  If so, use that field in the AutoNumber() instead of "Action Begda".

"Can you inform me how I can base the numbering only on the baseline filter?"

Include the fiter as a where clause in the LOAD that does the autonumbering,

-Rob

[robin_heijt]

Hi Rob,

Thank very much you for getting back to me.

The field "Action Begda" actually represents the date of the movement (dd.mm.yyyy). The only issue is that the sort order is based only on the day part. What I need is a sort order that is based on the full date range dd.mm.yyyy.

In regards to the baseline numbering. This is the code:

 Load *, 
 If("Movement Type" ='Lateral Move' , ('Baseline'),
 If("Movement Type" ='Promotion Band Up' , ('Baseline'),
 If("Movement Type" ='Promotion Within Band' , ('Baseline'),
 If("Movement Type" ='Termination Involuntary' , ('Baseline'),
 If("Movement Type" ='Termination Voluntary' , ('Baseline'), ('All Population') )))))  as "Adherence Filter";

Would you be able (and ofcourse willing to) prepare the code for me that does this:

Create the filter for baseline

Create a filter per year (01.07.yyyy-30.06.yyyy) (I have data starting July 2015 till now) "I call this a cycle"

Then assign a movement number based on the date of movement only for people that are in "Baseline"

And create the numbering for every cycle. So every new cycle the count starts over again.

I would be very grateful if you could find the time to do this for me, as this issue has been bothering me for weeks now.

Kind Regards,

Robin

[rwunderlich]

"The field "Action Begda" actually represents the date of the movement (dd.mm.yyyy). The only issue is that the sort order is based only on the day part. What I need is a sort order that is based on the full date range dd.mm.yyyy."

It sounds like you are not properly parsing the date when you read it.  If you did, "Order by "Action Begda"" would sort by the full date. See Get the Dates Right

-Rob

[robin_heijt]

I want to thank you very much Rob,

with the help of that link I was able to properly format the date, and it works now.

Now I am only stuck with the issue on the baseline filter and creating the sequence per cycle.

Could you advice on this aswell?

Kind regards,

Robin

[rwunderlich]

First create a new field that assigns a cycle based on your July - June fiscal year. Perhaps:

YearName("Action Begda", 0, 7) as Cycle

and then use the Cycle as extra ID in your Autonumber. Along with a where clause for your filter.

Sequence:

LOAD *

     AutoNumber("Action Begda", PERNR & Cycle) as MovementSequence

RESIDENT Movements

Where [Adherence Filter] = 'Baseline'

Order by "Action Begda",

;

-Rob