date to return a number

[dmxmikey]

I have dates  where i need to return the number of the day ie

17/09/18 to return 1

18/09/18 to return 2

19/09/18 to return 3

20/09/18 to return 4

21/09/18 to return 5

22/09/18 to return 6

23/09/18 to return 7

24/09/18 to return 1  etc

[Gysbert_Wassenaar · Accepted solution]

Perhaps: Weekday(MyDateField)+1

[vishsaggi]

Try this?

DateMap:

MAPPING LOAD * INLINE [

WeekDayName, ID

Mon,1

Tue,2

Wed,3

Thu,4

Fri,5

Sat,6

Sun,7

];

MapDates:

LOAD *,

     ApplyMap('DateMap',Text(WeekDay(Date#(Date2, 'DD/MM/YY')))) AS Day

     INLINE [

Date2

17/09/18

18/09/18

19/09/18

20/09/18

21/09/18

22/09/18

23/09/18

24/09/18

25/09/18

];