Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Try this:
for i=0 to 60
Data:
load * , Monthname(Date +($(i)*30) ) as <YourNewColumn>
resident <YourTableName> ;
next i
drop table <YourTableName> ;
YOur script is simply generating 60 months not taking into account "M000" should be Nov 2019,"M001" should be Dec 2019 and so on
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?
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 |
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;
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