QS- Where Clause

[Karahs]

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?

[vlad_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

[sunny_talwar · Accepted solution]

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];

[Karahs]

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

[jonathandienst]

>>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$);

[Karahs]

Hello Sunny T,

          Thanks for the answer it was helpful