Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to create transactions for a time period in a load script where I adding months within a time interval.
For example an action will happen every fourth month within the period 1.1.2014-30.6.2015 then I will need transactions with the following dates:
1.1.2014
1.5.2014
1.9.2014
1.1.2015
1.5.2015
Thank's
Not sure if this is what you are looking for but this script will generate transactions for you within a timespan and one event for every 4 months. You can introduce a random factor to filter out events too:
//Function to calculate Months Difference in script
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
vStart = MakeDate( 2014, 1 , 1);
vEnd = MakeDate( 2015, 6 , 30 );
Months = Ceil( $(MonthDiff(vStart,vEnd)) / 4 );
TRANSACTIONS:
LOAD
*
WHERE
Rand()<0.5; // Filter out randomly 50% of the events - if you want ALL set it to <1
LOAD
TransID & '-' & IterNo() AS TransID,
Date,
'Event #' & IterNo() AS EventID
WHILE
IterNo() < 10; // Generate 9 events for each month that comes from below
LOAD
RecNo() AS TransID,
AddMonths( '$(vStart)' , (RecNo()-1)*4 ) AS Date
AUTOGENERATE
$(Months);
Do you need to create rows with transactions?
Hi,
By using your date field ,
you can create a flag in Script,
If(Date>= AddMonths(Today(), -3), 1)as Last3MonthFlag
and in the front end you call it like,
Last3MonthFlag={1}
for 3 months data in chart to be displayed.
I hope this helps you .
Thanks,
Bunny
yes I need to create transaction to my fact table
Thank's,
But I think this will not help since I need to calculate the dates for occurrence
Dear peter ,
Please Help me in obtaining this Its working on my local System but not on Test Instance.
Pivot Table Current and Previous Using above
Thanks in Advance.
Bunny
Not sure if this is what you are looking for but this script will generate transactions for you within a timespan and one event for every 4 months. You can introduce a random factor to filter out events too:
//Function to calculate Months Difference in script
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
vStart = MakeDate( 2014, 1 , 1);
vEnd = MakeDate( 2015, 6 , 30 );
Months = Ceil( $(MonthDiff(vStart,vEnd)) / 4 );
TRANSACTIONS:
LOAD
*
WHERE
Rand()<0.5; // Filter out randomly 50% of the events - if you want ALL set it to <1
LOAD
TransID & '-' & IterNo() AS TransID,
Date,
'Event #' & IterNo() AS EventID
WHILE
IterNo() < 10; // Generate 9 events for each month that comes from below
LOAD
RecNo() AS TransID,
AddMonths( '$(vStart)' , (RecNo()-1)*4 ) AS Date
AUTOGENERATE
$(Months);
Thank you Petter
This solution will work 🙂