use the new value as job parameter for a loop
Moderators: chulett, rschirm, roy
use the new value as job parameter for a loop
After several hours of search, i found a very close help..
viewtopic.php?t=100588&highlight=passing+parameter
Please Let me know if that is applicable in my scene...
I need your help here!
What I have done:
I have a loop where I will process each month per run. So when user run for april, loop will run 4 times. This works fine for 1 to #PeriodNumber# with step/increment 1.
I capture the current loop run number $counter as #CurrPeriod# to use with file names or ... May be future use... for Begin_date lookup..like if #currPeriod#=1 then Use #JanBeginDate#
#currPeriod#=2 then use #FebBeginDate#... may be...
within loop, one job reads data from table where a very lengthy and complex query returns me snapshot for the month that the loop is processing. (so I can not have simple extract and compare)
Question: To get that snapshot, I need to pass a begin date( this date is not simple 1st of month, its business date that I can have from another query for all 12 months and put in a file) this begin_date should be passed as parameter.
So what shud be done so that each time when loop runs for perticular month, it wud read begin_date for that period and pass it as a job parameter. ( ie...snapshot_table_startDate=#JanBeginDate#)
Thanks a lot! Pls Do reply.
viewtopic.php?t=100588&highlight=passing+parameter
Please Let me know if that is applicable in my scene...
I need your help here!
What I have done:
I have a loop where I will process each month per run. So when user run for april, loop will run 4 times. This works fine for 1 to #PeriodNumber# with step/increment 1.
I capture the current loop run number $counter as #CurrPeriod# to use with file names or ... May be future use... for Begin_date lookup..like if #currPeriod#=1 then Use #JanBeginDate#
#currPeriod#=2 then use #FebBeginDate#... may be...
within loop, one job reads data from table where a very lengthy and complex query returns me snapshot for the month that the loop is processing. (so I can not have simple extract and compare)
Question: To get that snapshot, I need to pass a begin date( this date is not simple 1st of month, its business date that I can have from another query for all 12 months and put in a file) this begin_date should be passed as parameter.
So what shud be done so that each time when loop runs for perticular month, it wud read begin_date for that period and pass it as a job parameter. ( ie...snapshot_table_startDate=#JanBeginDate#)
Thanks a lot! Pls Do reply.
How exactly have you done what you have done? Meaning how / where is this loop implemented? That will help drive the answers.
If you are using the Loop stages in a Sequence job, why not pass in the current counter value as a parameter and use it against a hashed file that contains the 'BeginDate' for each period number? I'm sure something like that could be worked out fairly easily...
If you are using the Loop stages in a Sequence job, why not pass in the current counter value as a parameter and use it against a hashed file that contains the 'BeginDate' for each period number? I'm sure something like that could be worked out fairly easily...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for a quick reply..damn good...chulett wrote:How exactly have you done ....
yes I am using the start loop/end loop act in seq.
I can take $counter and go against hash to get begin date for that period...but how do I get that begin date to be a Job parameter?
Hope you get it..and its fairly easy
actully...I am good at DS but lil nervous for dead line ...I am sure I can follow the upper link...what say?
I might reply and develope this tomorow! its 1 15am et..need to get some sleep..
If I am alive only then I can use your bright ideas
Last edited by kunj201 on Thu Jul 13, 2006 11:24 pm, edited 1 time in total.
Does the date need to be a job parameter? Or can the counter be the job parameter and you use it to lookup the date inside the job?
If it needs to be a parameter, then look into the User Variables Stage and its documentation - that's exactly what it's for. Or write a job that takes the counter and looks up the date as noted, then stashes that value in the job's USERSTATUS area. Any job downstream from that job in the Sequence can use the $UserStatus value to feed a job parameter rather easily.
If it needs to be a parameter, then look into the User Variables Stage and its documentation - that's exactly what it's for. Or write a job that takes the counter and looks up the date as noted, then stashes that value in the job's USERSTATUS area. Any job downstream from that job in the Sequence can use the $UserStatus value to feed a job parameter rather easily.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I would not use $UserStatus if you need more than one value from a job. If you are on Ds7.5 then you can use routine arguments as parameter values or in loops. I would store dates into a hash file. It is easy to read a hashed file and return values in a routine. There is a utility routine to read any hashed file with any key value and return the whole record or one field. A routine needs to return @TRUE or @FALSE to continue. You may need to modify this routine to change arguments.
Routine:
ReadHashFile(HashedFileName, HashedFileKey, FieldToReturn, ValueReturned)
Make the routine return @TRUE if successful read and @FALSE if not. Then use a stage variable to set to ValueReturned. You could control the whole loop with routines. You could check if loop beyond EndDate in a routine. You could add one to the loop date in a routine. I do not see any limits now in how to do this in a sequence versus the old batch jobs. Very powerful.
Routine:
ReadHashFile(HashedFileName, HashedFileKey, FieldToReturn, ValueReturned)
Make the routine return @TRUE if successful read and @FALSE if not. Then use a stage variable to set to ValueReturned. You could control the whole loop with routines. You could check if loop beyond EndDate in a routine. You could add one to the loop date in a routine. I do not see any limits now in how to do this in a sequence versus the old batch jobs. Very powerful.
Mamu Kim
Build a sequence to control the process.
Your first job will get the business start date and load it to a hashed file with hardcoded key '1'.
In your second job , inside the job activity, in the Value expression of the job parameter "BeginDate", right click, go to DS Transform, go to sdk, then to Utility and choose
UtilityHashLookup(HashedFileName,'1',1)
This will return the date that you stored in the hashed file with key 1 and assign that to job parameter #BeginDate#.
Everymonth run both these jobs so that the hashed file gets updated with the new date.
Your first job will get the business start date and load it to a hashed file with hardcoded key '1'.
In your second job , inside the job activity, in the Value expression of the job parameter "BeginDate", right click, go to DS Transform, go to sdk, then to Utility and choose
UtilityHashLookup(HashedFileName,'1',1)
This will return the date that you stored in the hashed file with key 1 and assign that to job parameter #BeginDate#.
Everymonth run both these jobs so that the hashed file gets updated with the new date.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
How to ...
Seems very fesible option to follow...chulett wrote:...stashes that value in the job's USERSTATUS area. Any job downstream from that job in the Sequence can use the $UserStatus value to feed a job parameter rather easily.
Please let me know how exectly I attach that value in job's userStaus area ?
Option 2:
how to define the hash filename here ... It wont take simply name. do you mean hashfile.$UserStaus ??DSguru2B wrote:... go to DS Transform, go to sdk, then to Utility and choose
UtilityHashLookup(HashedFileName,'1',1)
This will return the date that you stored in the hashed file with key 1 and assign that to job parameter #BeginDate#.
Everymonth run both these jobs so that the hashed file gets updated with the new date.
I have UtilityHashLookup(HshCRDAreaPeriod.$UserStatus, '1', 1)
which gives me error:------------------
1264 p$V0S51$5 = (UtilityHashLookup((IdV6S0%%Result2%%6), ('2'), (2)))
^
',' unexpected, Was expecting: '!', ')', '=', "AND", "OR", "LT", "LE",
"GT", "GE", "NE", "EQ", "MATCH"
Array 'UtilityHashLookup' never dimensioned.
-----------------
Also, I have all the begin dates in a hash file as:
1,2006-01-03
2,2006-01-30
... for all 12months for this year.
So instead of '1' in UtilityHashLookup(HashedFileName,'1',1)
can I pass $Counter in Value expression so for Jan, it will return Jan Begin Date and in next loop run it wud take 2 as counter value and pass it thru utility and return feb begin date ?
Thanks all of you again....
-Nick
You dont need to pass $USERSTATUS. just the hashed file name. And yes pass the $counter as the key to the utility.
Do this
Do this
Code: Select all
UtilityHashLookup(HashedFileName, #$counter#,1)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
K i have loop..so loop will run from 1 to N period... I can have $counter as parameter that I can use for lookup against Hash file..kduke wrote:Routine:
ReadHashFile(HashedFileName, HashedFileKey, FieldToReturn, ValueReturned).....
...Then use a stage variable to set to ValueReturned.....
hash file:
1 2006-01-03
2 2006-01-30 ...till 12 2006-12-01 as begin date.
So I need only one value to be read as begin date for the current processing month and pass it to a #Begin_date#.. so in next loop run, it wud take 2....so can i use $counter or #currMonth# instead HashedFileKey ??
and also I must have the valueReturned as JOB PARAM which will be used in a query. stage variable won't help...right?
How to ...
UtilityHashLookup('H_Area_ICM_Period', #$counter#, 1)DSguru2B wrote:You dont need to pass $USERSTATUS. just the hashed file name. And yes pass the $counter as the key to the utility.
Do thisCode: Select all
UtilityHashLookup(HashedFileName, #$counter#,1)
Same error...
No pound/hash signs... and you need to qualify the counter with the name of the stage it comes from:
Code: Select all
Start_Loop_Stage_Name.$Counter
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
When I use in Manager to test the routine UtilityHashLookup....it says table not found...chulett wrote:No pound/hash signs... and you need to qualify the counter with the name of the stage it comes from:
Code: Select all
Start_Loop_Stage_Name.$Counter
Is it really a table or file?? Do I need to specity option while creating this hash file???
Pls try to comeup with solution to get the table name right...
see if it works for you...
Thanks.....
Last edited by kunj201 on Fri Jul 14, 2006 12:42 pm, edited 1 time in total.
Ok. Lets devise an easier solution to get the month. Is your cycle going to run after the first day or on the first day of every month. IF yes then instead of using $counter, you can just do,
Code: Select all
UtilityHashLookup('Fully qualified path of hashed file",Oconv(Date(),"DM"),1)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
I have tried that way...DSguru2B wrote:Ok. Lets devise an easier solution to get the month. Is your cycle going to run after the first day or on the first day of every month. IF yes then instead of using $counter, you can just do,Code: Select all
UtilityHashLookup('Fully qualified path of hashed file",Oconv(Date(),"DM"),1)
UtilityHashLookup("E:\Data\DEV\hashfiles\H_Area_ICM_Period","1",2)
and
UtilityHashLookup("E:\Data\DEV\hashfiles\H_Area_ICM_Period",Oconv(Date(),"DM"),1)
while compiling Seq: Error compiling Job Control Subroutine MySeqName
click on more:
(("E:\Data\DEV\hashfiles\H_Area_ICM_Period"), (Oconv(Date(), "DM")), (1)))
^
',' unexpected, Was expecting: '!', ')', '=', "AND", "OR", "LT", "LE",
"GT", "GE", "NE", "EQ", "MATCH"
Array 'UtilityHashLookup' never dimensioned.
2 Errors detected, No Object Code Produced.
(ICMAreaSeq)
Last edited by kunj201 on Fri Jul 14, 2006 12:57 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: