11 Replies Latest reply: Oct 16, 2014 3:56 AM by AVIRAL NAG RSS

    WeekDays Logic

    AVIRAL NAG

      Hi Community

       

      I want to insert Weekdays Mon, Tue, Wed, Thu, Wed, Thu, Fri, Sat where Nulls are coming in Weekdays column

       

      Logic to Apply:

       

      1): If Flag='DC' than insert Weekdays Mon, Tue, Wed, Thu, Wed, Thu, Fri

       

      2): If Flag='IDC' than insert Weekdays Mon, Tue, Wed, Thu, Wed, Thu, Fri, Sat

       

       

      Original Data:

       

      Originall111111.png

      Required Data:

      Requireddddddd1111.png

       

       

      PFA.

       

       

      Regards

      Av7eN

        • Re: WeekDays Logic
          Alkesh Sharma

          Create Weekdays in the master calender, it will pick the day of the week from the Master calender with corresponding Date,

           

          You just need to add a dimention as weekdays.

          • Re: WeekDays Logic
            Prem Kumar Thangallapally

            hi try this ,

             

             

             

            temp:

            Load *,RecNo() as RecNo;

            LOAD * INLINE [

                Serial No., TicketID, Creation Time, Flag, Start Time, End Time, WeekDays

                QYX00740, 1256670, 3/12/2014 5:24:05 PM, IDC, 9:30, 18:00,

                QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:30, 18:00,

                QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Mon

                QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Tue

                QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Wed

                QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Thu

                QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Fri

                QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Sat

                QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:00, 17:30, Thu

                QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:00, 17:30, Fri

            ];

            NoConcatenate

            FINAL_STAGE1:

            Load [Serial No.], TicketID, [Creation Time], Flag, [Start Time], [End Time]

            Resident temp where len(trim(WeekDays))=0;

            join

            LOAD * INLINE [

                WeekDays

                Mon

                Tue

                Wed

                Thu

                Fri

                Sat

            ];

            Concatenate

            Load *

            Resident temp where len(trim(WeekDays))>0;

            drop table temp;

            NoConcatenate

            FINAL_STAGE2:

            Load *

            Resident FINAL_STAGE1 Order by RecNo;

            drop table FINAL_STAGE1;

            • Re: WeekDays Logic
              jagan mohan rao appala

              Hi,

               

              Try like this

               

              Data:

              LOAD

              [Serial No], TicketID, [Creation Time], Flag, [Start Time], [End Time]

              FROM ExcelFile

              WHERE Len(Trim(WeekDays)) = 0;

               

              LEFT JOIN

              LOAD

              *

              INLINE [

              Flag, WeekDays

              DC, Mon

              DC, Tue

              DC, Wed

              DC, Thu

              DC, Fri

              IDC, Mon

              IDC, Tue

              IDC, Wed

              IDC, Thu

              IDC, Fri

              IDC, Sat];

               

              Concatenate(Data)

              LOAD

              [Serial No], TicketID, [Creation Time], Flag, [Start Time], [End Time],WeekDays

              FROM ExcelFile

              WHERE Len(Trim(WeekDays)) > 0;


              Hope this helps you.


              Regards,

              Jagan.

              • Re: WeekDays Logic
                Tresesco B

                A bit simpler:

                 

                Load
                *,
                WeekDay(IterNo()+1) as WeekDay
                While If(Flag='IDC',iterno()<=6, IterNo()<=5);

                Load * Inline [
                "Serial No.", TicketID, "Creation Time", Flag, "Start Time", "End Time"
                QYX00740, 1256670, 3/12/2014 5:24:05 PM, IDC, 9:30, 18:00
                QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:30, 18:00
                ]

                 

                Note: You might have to adjust 1,5,6 along with iterno() based on your day settings. For me day 0 is Saturday.

                  • Re: WeekDays Logic
                    Prem Kumar Thangallapally

                    hi try below for Little more simpler without loops and concatenation

                     

                     

                     

                    Load *,

                    if(len(trim(WeekDays))=0,if(Flag='IDC',SubField('Mon-Tue-Wed-Thu-Fri-Sat','-'),SubField('Mon-Tue-Wed-Thu-Fri','-')),WeekDays) as FINALWEEKDAYS

                    from PATH;

                      • Re: WeekDays Logic
                        AVIRAL NAG

                        Prem & Tresesco Sir I like both of yours Approach.

                        Great Work of Mind.

                         

                        Special Thanks to Jagan Mohan Sir for identifying duplicates in my Logic.

                        I was in so hurry that I missed that part of Duplicate Recoords. Later learnt that I was wrong.

                         

                        Thank you All for providing such wonderful solutions and that too instantly.

                         

                        Reagards

                        Av7eN