10 Replies Latest reply: Apr 13, 2018 7:35 AM by Imran Syed RSS

    Where condition failing

    Imran Syed

      Hi Experts,

       

      I have two tables.Joined them and created a third table using Resident for getting the data based on the condition of two fields coming from two  tables.

       

       

      TableA:

      X,Y

       

      TableB:

      X,Z

       

       

      TableC:

      X,Y,Z

      resident TableA

      where Y <=Z;

       

      so this where condition is not working?

       

      Where am I going wrong?

       

       

      Appreciate u r help.

        • Re: Where condition failing
          Sunny Talwar

          Join TableA and TableB so that you can perform the condition in where statement

           

          TableA:

          X,Y

           

          Join (TableA)

          X,Z

           

          TableC:

          X,Y,Z

          resident TableA

          where Y <=Z;

          • Re: Where condition failing
            Juan Olivares

            So well I don't understand your problem... could you post a sample table to see what happen to you, cause I agree with Sunny.  This is a simple case to solve with a Join

              • Re: Where condition failing
                Imran Syed

                Hi Juan,


                Here is the sample data.


                Temp:

                Load * Inline

                [

                    id, Customer, Sales,Clyr,Clyr1


                     1,A, 100,2012,2012


                     2,B, 200,2013,2013

                    

                     3,A, 300,2013,2013


                     4,C, 150,2014,2014


                     5,D, 200,2015,2015


                     6,B, 140,2016,2016


                     7,E, 400,2017,2017

                     8,g,200,2013,2013

                     9,C, 340,2018,2018

                     10,F,600,2019,2019

                ];


                outer Join(Temp)

                Final:

                Load * Inline

                [

                    Clyr,MCl,MClyr


                    2018,2018,03

                   

                     ];



                NoConcatenate


                ts:

                load *

                Resident Temp

                where Clyr1 <= MCl;



                drop Table Temp;

                 

                Here I am getting only yr 2018 data.Instead I want to get  2012 to 2018.

                Please can you suggest me where i am going wrong.

                  • Re: Where condition failing
                    Sunny Talwar

                    Try this - Basically don't join on C1yr... just do a Cartesian join

                     

                    Temp:

                    LOAD * INLINE [

                        id, Customer, Sales, Clyr, Clyr1

                        1, A, 100, 2012, 2012

                        2, B, 200, 2013, 2013

                        3, A, 300, 2013, 2013

                        4, C, 150, 2014, 2014

                        5, D, 200, 2015, 2015

                        6, B, 140, 2016, 2016

                        7, E, 400, 2017, 2017

                        8, g, 200, 2013, 2013

                        9, C, 340, 2018, 2018

                        10, F, 600, 2019, 2019

                    ];



                    Join(Temp)

                    LOAD MCl,

                    MClyr;

                    LOAD * INLINE [

                        Clyr, MCl, MClyr

                        2018, 2018, 03

                    ];


                    NoConcatenate

                    ts:

                    LOAD *

                    Resident Temp

                    Where Clyr1 <= MCl;


                    DROP Table Temp;