Generate rows using weeknumber?

stefanem

Hello,

I use an internal weeknumber and i would want calculate it using the calendar week number.

For example, calendar week for the week 26 = internal week number 1974, then week 27 should be 1975, etc

i would want to generate it automatically.

I've alreay create a job to get the calendar weeknumber, but i don't find how to proceed then.

in attachment my job, all advice are welcome :-)

thank you

Stephane

schema.PNG

Result.PNG

Output

Best Practices

Preparation

Accepted answers

lminors

Sorry @stefanem I made a bit of a mistake. The formula should read:

IF IsNull([Row-1:Current Week])

THEN 1974

ELSEIF [Row-1:Current Week] = [Current Week]
THEN [Row-1:Internal Week]
ELSE [Row-1:Internal Week] + 1
ENDIF

Please see attached solution so you can see it working.

InternalWeekGen.yxmd

All comments

lminors

Hi @stefanem,

The Multi-Row formula tool should solve this problem as it will enable you to compare the the current row for 'Current Week' with the row before and update the 'Internal Week' field as appropriate. Your expression will look like:

IF [Row-1:Current Week] = [Current Week]
THEN [Internal Week]
ELSE [Internal Week] +1
ENDIF

Or you could make it even more bulletproof with:

IF [Row-1:Current Week] = [Current Week]
THEN [Internal Week]
ELSE [Internal Week] + ([Current Week]-[Row-1:Current Week])
ENDIF

Does this solve your problem? Experiment with that tool as I'm fairly sure it will be of help for you here.

Regards,

Luke

Alteryx Certified Partner for Keyrus UK

lminors

P.S. This relies on you already having the first one in there (as you showed in your screenshot) - if you don't and you want to generate it from scratch then you just have to build that into the formula similar to this:

IF IsNull([Row-1:Current Week]) <---This is a test for if it is the first field (Could also be IF [Row-1:Current Week] = 26)

THEN 1974

ELSEIF [Row-1:Current Week] = [Current Week]
THEN [Internal Week]
ELSE [Internal Week] + ([Current Week]-[Row-1:Current Week])
ENDIF

Forgot to add that the tool should be set up to 'Update Existing Field' as Internal Week.

Let me know how you get on.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335