2 Replies Latest reply: Feb 6, 2012 6:06 PM by Henric Cronström RSS

    Set Variable from fieldname

    frank volpe

      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

        • Set Variable from fieldname

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

           

           

          hope this will help ...

          • Set Variable from fieldname
            Henric Cronström

            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.