use the new value as job parameter for a loop

[kunj201]

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.

[chulett]

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...

[kunj201]

How exactly have you done ....

Thanks for a quick reply..damn good...
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

[chulett]

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.

[kduke]

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.

[DSguru2B]

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.

[kunj201]

...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.

Seems very fesible option to follow...
Please let me know how exectly I attach that value in job's userStaus area ?

Option 2:

... 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.

how to define the hash filename here ... It wont take simply name. do you mean hashfile.$UserStaus ??
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

[DSguru2B]

You dont need to pass $USERSTATUS. just the hashed file name. And yes pass the $counter as the key to the utility.
Do this

Code: Select all

UtilityHashLookup(HashedFileName, #$counter#,1)

[kunj201]

Routine:
ReadHashFile(HashedFileName, HashedFileKey, FieldToReturn, ValueReturned).....
...Then use a stage variable to set to ValueReturned.....

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..
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?

[kunj201]

You dont need to pass $USERSTATUS. just the hashed file name. And yes pass the $counter as the key to the utility.
Do this

Code: Select all

UtilityHashLookup(HashedFileName, #$counter#,1)

UtilityHashLookup('H_Area_ICM_Period', #$counter#, 1)

Same error...

[chulett]

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

[kunj201]

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

When I use in Manager to test the routine UtilityHashLookup....it says table not found...
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.....

[DSguru2B]

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)

[kunj201]

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)

I have tried that way...
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)

[ray.wurlod]

Bummer. UtilityHashLookup does not work with pathed hashed files. You could readily adapt it, of course.