5 Replies Latest reply: Apr 28, 2016 5:11 AM by Kshitija Shinde RSS

    QS- Where Clause

    Kshitija Shinde

      Hello Everyone,

                      I have a user created field after the resident load so i want to use where clause for that user created field.

       

      For eg.

      [Online Offline]:

      LOAD

      [Region],

        [Branch] as Master_Branch,

        [Client Code]as [Party Code],

        [Equity Online],

        [Equity Offline],

        [Commodity Online],

        [Commodity Offline],

        [Currency Online],

        [Currency Offline],

        [Total Brokerage],

        [Total Traded Days],

          IF([Equity Offline] >= 1500,1,0) as EquityOfflineFlag,

          IF([Commodity Offline] >= 1500,1,0) as CommodityOfflineFlag,

          IF([Currency Offline] >= 1500,1,0) as CurrencyOfflineFlag

      FROM [lib://Online Offline/online offline.xls]

      (biff, embedded labels, table is Sheet1$)

       

      Table2:

      Load

      [Region],

      Master_Branch,

      [Party Code],

      [Equity Online],

      [Equity Offline],

      [Commodity Online],

      [Commodity Offline],

      [Currency Online],

      [Currency Offline],

      [Total Brokerage],

      [Total Traded Days],

          EquityOfflineFlag,

          CommodityOfflineFlag,

          CurrencyOfflineFlag,

          IF( EquityOfflineFlag = 1 or CommodityOfflineFlag =1 or CurrencyOfflineFlag =1 , 'Consider Data' , 'Dont Consider Data') as DATACONSIDERATION

          

      Resident [Online Offline];

      Drop Table [Online Offline];

       

       

      I want to use a where clause For the field 'DATACONSIDERATION', i tried using where DATACONSIDERATION='Consider data' but it isnt working. Can anyone help me out?

        • Re: QS- Where Clause
          Vladimir Komarov

          In your code above you are using different spelling in the DATACONSIDERATION field values:

          'Consider Data' vs. 'Consider data'


          If this is the exact code,  it's the reason why where clause in your code is not working...


          VK

           

          • Re: QS- Where Clause
            Sunny Talwar

            Not sure where exactly were you planning to use the where statement? Table2 resident load? The field doesn't even exist right now. You cannot use a where clause on a field getting created in the same table. You can use the same logic though (see below) or you can use the where clause in the preceding load. I would suggest the 1st alternative just so you can avoid a unnecessary preceding load, but just so you know the other option exists also

             

            Try this:

             

            [Online Offline]:

            LOAD

            [Region],

              [Branch] as Master_Branch,

              [Client Code]as [Party Code],

              [Equity Online],

              [Equity Offline],

              [Commodity Online],

              [Commodity Offline],

              [Currency Online],

              [Currency Offline],

              [Total Brokerage],

              [Total Traded Days],

                IF([Equity Offline] >= 1500,1,0) as EquityOfflineFlag,

                IF([Commodity Offline] >= 1500,1,0) as CommodityOfflineFlag,

                IF([Currency Offline] >= 1500,1,0) as CurrencyOfflineFlag

            FROM [lib://Online Offline/online offline.xls]

            (biff, embedded labels, table is Sheet1$)

             

            Table2:

            Load

            [Region],

            Master_Branch,

            [Party Code],

            [Equity Online],

            [Equity Offline],

            [Commodity Online],

            [Commodity Offline],

            [Currency Online],

            [Currency Offline],

            [Total Brokerage],

            [Total Traded Days],

                EquityOfflineFlag,

                CommodityOfflineFlag,

                CurrencyOfflineFlag,

                IF( EquityOfflineFlag = 1 or CommodityOfflineFlag =1 or CurrencyOfflineFlag =1 , 'Consider Data' , 'Dont Consider Data') as DATACONSIDERATION

            Resident [Online Offline]

            Where EquityOfflineFlag = 1 or CommodityOfflineFlag =1 or CurrencyOfflineFlag =1;

             

            Drop Table [Online Offline];

              • Re: QS- Where Clause
                Kshitija Shinde

                Hello

                     When i use that code which you have posted i get an error saying garbage after statement. What is your second alternative?

                      My main requiremnt is that DATACONSIDERATION is a column in which 'Consider Data' and 'Dont Consider Data' are two elements, and i need to show only 'Consider Data' in that DATACONSIDERATION column.That is why i used where clause which is giving me an error. So what do you suggest me  to do? Please Help!

                 

                Thanks

                  • Re: QS- Where Clause
                    Jonathan Dienst

                    >>When i use that code which you have posted i get an error saying garbage after statement

                     

                    This usually means that a semi-colon is missing or is in the wrong place. Check that your code is the same as what Sunny posted, which looks correct to me.

                     

                    EDIT = I see that the code is missing a semi-colon after the first load:

                     

                    FROM [lib://Online Offline/online offline.xls]

                    (biff, embedded labels, table is Sheet1$);

                  • Re: QS- Where Clause
                    Kshitija Shinde

                    Hello Sunny T,

                     

                              Thanks for the answer it was helpful