Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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];
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
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];
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
>>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$);
Hello Sunny T,
Thanks for the answer it was helpful