topic Re: QS- Where Clause in App Development

QS- Where Clause

Karahs — Wed, 27 Apr 2016 06:07:15 GMT

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

vlad_komarov — Wed, 27 Apr 2016 19:27:43 GMT

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...

Re: QS- Where Clause

sunny_talwar — Wed, 27 Apr 2016 19:31:05 GMT

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

Karahs — Thu, 28 Apr 2016 05:17:10 GMT

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

jonathandienst — Thu, 28 Apr 2016 05:30:29 GMT

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

Karahs — Thu, 28 Apr 2016 09:11:24 GMT

Hello Sunny T,

Thanks for the answer it was helpful