9 Replies Latest reply: Sep 10, 2015 5:10 AM by appi karna RSS

    "Where" Clause

    appi karna

      Hi,

      Need help in reducing the file size by applying the following extraction criteria:

      1. PO number is less than "9000000000" greater than "9999999999".

      2. Field [ETA Date] is >= 01/01/2015, or Field [ATA DATE] is >= 01/01/2015

      I need to reduce the number of records in the output.

       

      I am currently giving the condition as shown in the table below but I am not getting the expected outcome:

       

      The only outcome that is satisfied is the  "PO number is less than "9000000000" greater than "9999999999" but the second condition is not met. Let me know if I missed anything.

       

      Test:

      LOAD [PO Number],

          
      [PO Item Number] ,

        [Delivery Number] ,

       
      [Delivery Item] ,

       
      [Shipment Number] ,

        [ETA Date] ,

       
      [ATA Date] ,

      Resident Delivery


      where [ETA Date]>= 01/01/2015 and [ATA Date]>= 01/01/2015

       
      and [PO Number]<= 9000000000 or [PO Number]>= 9999999999;

       

       

       

       

        • Re: "Where" Clause
          Oleg Troyansky

          If your date fields are formatted as "proper" QlikView (or Microsoft) dates, then you should enclose your date value in single quotes:

           

          where [ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015'

           

          Otherwise, you may need to transform your dates into the proper format.

           

          cheers,

          Oleg Troyansky

          QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

          • Re: "Where" Clause
            Marco Wedel

            you might also need some brackets here:

            ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);

            to get the correct logic as 'and' has a higher priority than 'or'



            regards

             

            Marco

              • Re: "Where" Clause
                Marco Wedel

                one example using the proposed solutions:

                 

                QlikCommunity_Thread_179828_Pic1.JPG

                 

                Test:
                LOAD [PO Number],
                    [ETA Date],
                    [ATA Date]
                Inline [
                    PO Number, ETA Date, ATA Date
                    8999999997, 01/01/2015, 01/01/2015
                    8999999998, 12/31/2014, 01/02/2015
                    8999999999, 01/02/2015, 12/31/2014
                    9000000000, 01/02/2015, 01/02/2015
                    9000000001, 01/02/2015, 01/02/2015
                    9000000002, 01/02/2015, 01/02/2015
                    9000000003, 01/02/2015, 01/02/2015
                    9999999998, 01/02/2015, 01/02/2015
                    9999999999, 01/02/2015, 01/02/2015
                    10000000000, 01/02/2015, 01/02/2015
                    10000000001, 12/31/2014, 01/02/2015
                    10000000002, 01/02/2015, 12/31/2014
                    10000000003, 12/31/2014, 12/31/2014
                ]
                Where [ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015'
                  and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);
                

                 

                other expressions for

                 

                [ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015'
                

                 

                could be

                 

                RangeMin([ETA Date],[ATA Date])>= '01/01/2015'
                

                 

                or

                 

                Year(RangeMin([ETA Date],[ATA Date]))>= 2015
                

                 

                as well.

                 

                hope this helps

                 

                regards

                 

                Marco

                  • Re: "Where" Clause
                    Marco Wedel

                    if 'MM/DD/YYYY' is not your default DateFormat, then it might be necessary to interpret your date string like

                     

                    Where [ETA Date]>= Date#('01/01/2015','MM/DD/YYYY') and [ATA Date]>= Date#('01/01/2015','MM/DD/YYYY')

                     

                     

                    Test:
                    LOAD *
                    Where [ETA Date]>= Date#('01/01/2015','MM/DD/YYYY') and [ATA Date]>= Date#('01/01/2015','MM/DD/YYYY')
                      and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);
                    LOAD [PO Number],
                        Date#([ETA Date],'MM/DD/YYYY') as [ETA Date],
                        Date#([ATA Date],'MM/DD/YYYY') as [ATA Date]
                    Inline [
                        PO Number, ETA Date, ATA Date
                        8999999997, 01/01/2015, 01/01/2015
                        8999999998, 12/31/2014, 01/02/2015
                        8999999999, 01/02/2015, 12/31/2014
                        9000000000, 01/02/2015, 01/02/2015
                        9000000001, 01/02/2015, 01/02/2015
                        9000000002, 01/02/2015, 01/02/2015
                        9000000003, 01/02/2015, 01/02/2015
                        9999999998, 01/02/2015, 01/02/2015
                        9999999999, 01/02/2015, 01/02/2015
                        10000000000, 01/02/2015, 01/02/2015
                        10000000001, 12/31/2014, 01/02/2015
                        10000000002, 01/02/2015, 12/31/2014
                        10000000003, 12/31/2014, 12/31/2014
                    ];
                    

                     

                    hope this helps

                     

                    regards

                     

                    Marco

                • Re: "Where" Clause
                  Sudeep Mahapatra

                  I think it would be wise to declare the date in a variable and then use the variable in your script.

                  let vDate = <yourdate>;

                   

                  [ETA Date] is >= '$(vDate)';

                  • Re: "Where" Clause
                    Rupesh Bharadwaj

                    Try this:

                     

                    where [ETA Date]>= 01/01/2015 and [ATA Date]>= 01/01/2015 and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);

                    • Re: "Where" Clause
                      Sasidhar Parupudi

                      Test:

                       

                      LOAD [PO Number],

                       

                           [PO Item Number] ,

                       

                        [Delivery Number] ,

                       

                        [Delivery Item] ,

                       

                        [Shipment Number] ,

                       

                        [ETA Date] ,

                       

                        [ATA Date] ,

                       

                      Resident Delivery

                       

                       

                      where ([ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015')

                       

                        and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);