Loops in the Script to Add MOnths to a Date Field

[Rehan]

I have a Date field in the script  and a Month field. Month field values are "M000", "M001", "M002" and up to "M060"

I would like to name those fields as Date + 30 day incremental.

For example "M000" should be Monthname(Date +0 )

"M001" should be Monthname(Date +30 )

"M002" should be Monthname(Date +60 ) and so o so forth

I can do that with a If statement like 

If(Month='M000',Monthname(Date+0),
If(Month='M001',Monthname(Date+30),
 If(Month='M002',Monthname(Date+60)))) etc .

But I am looking for a more robust solution using a For loop or something so I don't have to do 60 if statements.

Can anyone help. Please see the attached sample file

[Arthur_Fong]

Try this:

for i=0 to 60

Data:

load * ,  Monthname(Date +($(i)*30) ) as <YourNewColumn>

resident <YourTableName> ;

next i

drop table  <YourTableName> ;

[Rehan]

YOur script is  simply generating 60 months not taking into account "M000" should  be  Nov 2019,"M001" should be Dec 2019 and so on 

[Arthur_Fong]

I don't really get your requirement. This is your example given:

Date	PN	Desc	Part	Month	Data
19/11/2019	A76899	ABC	2	M000	87
19/11/2019	A76899	ABC	2	M001	8
19/11/2019	A76899	ABC	2	M002	28

In Date column is showing November data, and Month column showing M000,M001 and M002.

But your requirement is M000=Nov , M001=Dec and so on.

So how is this Date column actually can be linked to M001 and M002?

[Arthur_Fong]

Based on your requirement, sample data should looks like this:

Date	PN	Desc	Part	Month	Data
19/11/2019	A76899	ABC	2	M000	87
19/11/2019	A76899	ABC	2	M000	8
19/11/2019	A76899	ABC	2	M000	28

[Arthur_Fong · Accepted solution]

Is this the expected result?

Script:

Directory;

Raw:
LOAD
Date#(Date,'DD/MM/YYYY')AS Date,PN,Desc,Part,Month,Data,Date&Month as Key inline [
Date,PN,Desc,Part,Month,Data
19/11/2019,A76899,ABC,2,M000,87
19/11/2019,A76899,ABC,2,M001,8
19/11/2019,A76899,ABC,2,M002,28
];

NoConcatenate
loopData:
load distinct
Key
resident Raw;

let vRow=NoOfRows('loopData');

for i=1 to $(vRow)
let vMonth=peek('Key',$(i)-1,'loopData');

Data:

load * , Monthname(Date +(($(i)-1)*30) ) as MonthNew

resident Raw
where Key='$(vMonth)';

trace $(i) &':'&$(vMonth);
next i

drop table Raw ;

drop table loopData;

[Brett_Bleess]

Rehan, did Arthur's latest post help you get what you needed?  If so, please be sure to return to the thread and use the Accept as Solution button to give him credit and to let other Community Members know it helped.  If you are still working upon things, please provide an update and clarification per Arthur's comments such that we can see if we can help get things sorted.  If you figured things out, please consider posting what you did and then marking that post as the solution in that case.

Regards,
Brett