6 Replies Latest reply: Oct 5, 2017 5:04 AM by abhay singh RSS

    Logic transformation Help

    abhay singh

      Hi Guys ,

       

      Please see into excel, Dummy data with desired output. please suggest.

       

      Thanks in Advance

        • Re: Logic transformation Help
          Tresesco B

          Is this logic Date dependent? What if one status is red in one day and not reported in another date?

          • Re: Logic transformation Help
            Vineeth Pujari

            Try like below

             

            FACT:

            LOAD ID,

                 dual(RAGStatus,Match(RAGStatus,'Green','NotReported','Red')) as RAGStatus,

                 Date,

                 AutoNumber(ID&Date) as ID_DT_Grp

             

            FROM

            [Excelfilepath]

            (ooxml, embedded labels, table is Sheet1);

             

             

            left join(FACT)

             

             

            load ID_DT_Grp, Pick(Max(RAGStatus) ,'Green','NotReported','Red') as OUTPUT

            Resident FACT

            Group by ID_DT_Grp;

            • Re: Logic transformation Help
              Kavita Vishwakarma

              I think there might be slight change as follows:


              FACT:

              LOAD ID,

              dual(RAGStatus,Match(RAGStatus, 'NotReported','Green' ,'Red')) as RAGStatus,

                   Date,

                   AutoNumber(ID&Date) as ID_DT_Grp

               

              FROM

              [Excelfilepath]

              (ooxml, embedded labels, table is Sheet1);

               

               

              left join(FACT)

               

               

              load ID_DT_Grp, Pick(Max(RAGStatus) , 'NotReported','Green' ,'Red') as OUTPUT

              Resident FACT

              Group by ID_DT_Grp;

               

               

              As the above code..gives Green & not reported as Not Reported.. You want Green & Not Reported as Green.

              • Re: Logic transformation Help
                abhay singh

                I tried in this way

                 

                [Sheet1]:

                LOAD [ID],

                  //[F2],

                  [RAGStatus],

                  [Date]

                // [F5],

                // [Output required],

                // [F7],

                // [Remark]

                FROM [lib://AttachedFiles/Dummy Data.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                  left join

                load [ID],

                  [Date],

                only(if(RAGStatus= 'Red','Red')) as test,

                only(if(RAGStatus= 'Green','Green')) as test1,

                only(if(RAGStatus= 'NotReported','NotReported')) as test2

                Resident Sheet1 Group by [ID],[Date];

                 

                and at front end

                 

                if(not IsNull(test),test,

                if(not IsNull(test1),test1,

                if(not IsNull(test2),test2)))

                 

                working fine.