Set Variable from fieldname

[#1]

I'm tying to set a variable $(ToDate) to the value contained in a filedname (EndDate) so I can use it in a  subsequent load SQL statement.  I tried using the peek statement but I believe that can only be used in the load statement.  Any ideas? Thanks.

DatePatternTo:
Load
if ($(sub) = 1, CDD01J ,
if ($(sub) = 2, CDD02J ,
if ($(sub) = 3, CDD03J ,
if ($(sub) = 4, CDD04J ,
if ($(sub) = 5, CDD05J ,
if ($(sub) = 6, CDD06J ,
if ($(sub) = 7, CDD07J ,
if ($(sub) = 8, CDD08J ,
if ($(sub) = 9, CDD09J ,
if ($(sub) = 10, CDD10J ,
if ($(sub) = 11, CDD11J ,
if ($(sub) = 12, CDD12J )))))))))))) as EndDate
;
SQL SELECT CDFY,CDDFYJ,CDD01J,CDD02J,CDD03J,CDD04J,CDD05J,CDD06J,CDD07J,CDD08J,CDD09J,CDD10J,CDD11J,CDD12J  
FROM "JDE_PRODUCTION".PRODDTA.F0008 where CDFY = $(PrevYear2) and CDDTPN = 'R';

Set $(ToDate) = EndDate;

SalesDetails:

Load

'N/A' as Week,

'N/A' as Day,

'N/A' as WeekDay,

'N/A' as [Week Ending Date],

.

.

.

SQL SELECT

SDFY,SDLNTY,SDITM,SDDOCO,SDDOC,SDDCT,SDMCU ,SDFY,SDAN8 ,SDSHAN,SDIVD,SDDGL ,SDLITM,SDDSC1,SDDSC2,SDUOM ,SDSOBK,SDADDJ,

SDUORG,SDSOQS ,SDITWT,SDWTUM,SDUPRC,SDRSDJ,SDLTTR,SDDCTO,SDRCD,SDAEXP,SDLNID,SDUNCS,SDECST,SDDCTO,SDTRDJ,SDPA8,SDURAT, SDFRTH, SDCARS

FROM "JDE_PRODUCTION".PRODDTA.F42119

where SDIVD >= $(FromDate) and SDIVD <= $(ToDate)

and SDLNTY IN ('A','C','D','E','F','G','H','S','M','N','Q','K','R','Y','YR','Z','FP','SC')

and SDDCTO IN ('SO','SV','CO','CV','CM','SZ','SD','S1','SR')

and SDLTTR > '579'

and SDNXTR > '619'

and SDSHAN > 500

[#2]

plz try peek function to get end date vaue and store that in a variable.

hope this will help ...

[hic]

The peek function can be used outside Load statements, typically in a Let statement, e.g. Let $(vToDate) = peek(...); The problem you face is to choose which record to use - you most likely have several end dates in the table. Try something similar to the following  (just one value):

MaxDate:

Load Max(Date) as EndDate resident FirstTable ;

Let vToDate = peek('EndDate',-1,'MaxDate');

Further, you should probably look at the Crosstable prefix to collapse your CDD01J .. CDD12J columns into one column.