Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Below is my expected requirement. I have a field YearMonth.
YearMonth | As of filed |
201803 | 201803 |
201806 | 201803 |
201806 | 201806 |
201809 | 201803 |
201809 | 201806 |
201809 | 201809 |
201812 | 201803 |
201812 | 201806 |
201812 | 201809 |
201812 | 201812 |
201903 | 201806 |
201903 | 201809 |
201903 | 201812 |
201903 | 201903 |
I would probably do it something like this:
AsOfMonth:
load
//FP as Month_AsOf,
FP,
//Autonumber(FP) + 1 - IterNo() as FP,
If(Month+3*(IterNo()-1)>12, (Year+1)*100+(Month+3*(IterNo()-1)-12),
FP + 3*(IterNo()-1)) as Month_AsOf
while IterNo() <= 4;
T1:
LOAD *,
Left(FP,4) as Year,
Right(FP,2) as Month
INLINE [
FP
201803
201706
201809
201903
201812
];
Hi Tresesco,
I got it running, like this:
T1:
LOAD * INLINE [
FP
201803
201806
201809
201812
201903
];
AsOfMonth:
load
FP as Month_AsOf,
Autonumber(FP) + 1 - IterNo() as FP
Resident T1
while IterNo() <= 4;
right join load Autonumber(FP) as FP,FP as Date Resident T1;
Drop table T1;
I shuffled the fields in the requirement, my bad
Is this working for you? I guess this solution has some glitch. Are you doing this for months or quarters?
Yes, it seems to be working for me. Can you please help me with the glitch ?
I have to do this for quarters, but i just converted the Qtr to sequential number using Auto number to make it easy
I then used Month_Asof as my dimension.
Yes, that is true.
Can you please suggest me a better approach.
I would probably do it something like this:
AsOfMonth:
load
//FP as Month_AsOf,
FP,
//Autonumber(FP) + 1 - IterNo() as FP,
If(Month+3*(IterNo()-1)>12, (Year+1)*100+(Month+3*(IterNo()-1)-12),
FP + 3*(IterNo()-1)) as Month_AsOf
while IterNo() <= 4;
T1:
LOAD *,
Left(FP,4) as Year,
Right(FP,2) as Month
INLINE [
FP
201803
201706
201809
201903
201812
];
Thanks a lot for the solution 🙂
I was struggling with this logic:
If(Month+3*(IterNo()-1)>12, (Year+1)*100+(Month+3*(IterNo()-1)-12),
FP + 3*(IterNo()-1))
So used a short cut 😛