topic Re: Loop through field and assign a number in App Development

Loop through field and assign a number

robin_heijt — Tue, 25 Sep 2018 16:50:24 GMT

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.

Re: Loop through field and assign a number

vamsee — Tue, 25 Sep 2018 16:59:41 GMT

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

Re: Loop through field and assign a number

robin_heijt — Tue, 25 Sep 2018 17:13:52 GMT

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.

Re: Loop through field and assign a number

rwunderlich — Tue, 25 Sep 2018 17:30:44 GMT

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

Re: Loop through field and assign a number

robin_heijt — Tue, 25 Sep 2018 23:21:15 GMT

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.

Re: Loop through field and assign a number

rwunderlich — Wed, 26 Sep 2018 14:38:05 GMT

"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

Re: Loop through field and assign a number

robin_heijt — Wed, 26 Sep 2018 15:36:18 GMT

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

Re: Loop through field and assign a number

rwunderlich — Wed, 26 Sep 2018 15:48:39 GMT

"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

Re: Loop through field and assign a number

robin_heijt — Thu, 27 Sep 2018 16:27:30 GMT

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

Re: Loop through field and assign a number

rwunderlich — Fri, 28 Sep 2018 16:38:11 GMT

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

Re: Loop through field and assign a number

robin_heijt — Mon, 01 Oct 2018 16:38:49 GMT

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.

Re: Loop through field and assign a number

rwunderlich — Mon, 01 Oct 2018 17:39:05 GMT

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

Re: Loop through field and assign a number

robin_heijt — Wed, 10 Oct 2018 07:35:54 GMT

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.

Re: Loop through field and assign a number

rwunderlich — Wed, 10 Oct 2018 17:26:46 GMT

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