6 Replies Latest reply: May 22, 2014 5:22 AM by Rachel Boey RSS

    derived fields in master calendar script does not exist

      Hi, does anyone know why all the fields in my master calendar code does not exist? I cant find the fields when I want to create listbox. My master calendar code is in another tab in edit script and the script could load, just that I can't find any of the master calendar fields. Thanks.

        • Re: derived fields in master calendar script does not exist
          anbu cheliyan

          Can you attach your app.

          • Re: derived fields in master calendar script does not exist
            Pradip Sen

            Upload your sample file.

              • Re: derived fields in master calendar script does not exist

                Main tab:

                 

                load *,If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',DESCRIPTION) as Description,

                    If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',SITEID) as [Site ID],

                    If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',PURCHASEAGENT) as [Purchase Agent],

                    If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',REQUESTEDBY) as [Requested By],

                    If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',RFQNUM) as RFQnum,

                    If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',STATUS) as Status;

                 

                RFQ:    

                LOAD

                .

                .

                .(the rest of the fields)

                .

                .

                     date(floor(timestamp#(Replace([PRINTDATE],' ',' '), 'DD.MM.YYYY hh:ss:mm'))) as [Print Date]    

                FROM

                <filename>

                (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                 

                 

                Dates tab:

                QuartersMap: 

                MAPPING LOAD  

                rowno() as Month, 

                'Q' & Ceil (rowno()/3) as Quarter 

                AUTOGENERATE (12);

                 

                 

                Temp: 

                Load 

                               min([Print Date]) as minDate, 

                               max([Print Date]) as maxDate 

                Resident RFQ; 

                 

                Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                DROP Table Temp; 

                 

                TempCalendar: 

                LOAD 

                               $(varMinDate) + Iterno()-1 As Num, 

                               Date($(varMinDate) + IterNo() - 1) as TempDate 

                               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                 

                MasterCalendar: 

                Load 

                               TempDate AS [Print Date], 

                               week(TempDate) As Week, 

                               Year(TempDate) As Year, 

                               Month(TempDate) As Month, 

                               Day(TempDate) As Day, 

                               YeartoDate(TempDate)*-1 as CurYTDFlag, 

                               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                               date(monthstart(TempDate), 'MM/YYYY') as MonthYearNum,

                               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                               WeekDay(TempDate) as WeekDay 

                Resident TempCalendar 

                Order By TempDate ASC; 

                Drop Table TempCalendar;