Load flag field

[danielnevitt]

Dear all,

I'm trying to create a flag field which indicates where there are both Buys and Sells (field name 'Side') on specific days (field name 'TransactDate') and for specific products (field name 'Symbol')

As an example:

TransactDate                   Symbol                   Side
04/08/2020                       123                           Buy
04/08/2020                       123                           Sell
04/08/2020                       456                           Buy
04/08/2020                       456                           Buy

Using the above data I would like the new flag field to show 'Y' for product 123 on date 04/08/2020 and 'N' for product 456 on 04/08/2020.

Regards, 
Daniel

[Anonymous]

Hello,

using the following script, I get a new column named flag which shows Y for Symbol 123 (both data rows) and N for 456
Is it what you want? The solution using concat is one possibility to achieve it. Hope it helps!

TEST:
LOAD * INLINE [
TransactDate, Symbol, Side
04/08/2020, 123, Buy
04/08/2020, 123, Sell
04/08/2020, 456, Buy
04/08/2020, 456, Buy
];

TEST1:
NoConcatenate load
TransactDate,
Symbol,
concat(Side,'/') as Sidenew
Resident TEST
group by TransactDate, Symbol;

left join(TEST)
load *,
if (Sidenew='Buy/Sell' or Sidenew='Sell/Buy','Y','N') as Flag
resident TEST1;

drop table TEST1;

[danielnevitt]

Hi,

Thank you for the quick reply.  Is it possible to create a single line in the code?  I'm not sure how I would add your solution to the full sql that I have.

I was hoping I could create something like the below:

If(Count({<Side={'Buy'}>}TransactDateSymbol)>0 and Count({<Side={'Sell'}>}TransactDateSymbol)>0,'Y') where TransactDateSymbol is a concatenated field of TransactDate and Symbol, however I'm not sure if this makes sense or is possible.

Thanks,
Daniel

[Kushal_Chawda · Accepted solution]

try below

=if(sum(total <TransactDate,Symbol>aggr(Count({<Side={'Buy'}>+{<Side={'Sell'}>}Side),TransactDate,Symbol))>=1,'Y','N')

[danielnevitt]

Thank you so much, that works perfectly in the expression in the table I am using.

Do I need to amend the code in any way to use it as part of the load script.

I really appreciate the help with this.

Regards,
Daniel

[Kushal_Chawda]

You don't need to amend any code changes. Just use it in your expression

[danielnevitt]

Thanks again for the reply.

I was hoping to create a field, so that I can filter by that,as I would ideally want to show only the results for 'Y'.

Regards,
Daniel

[Kushal_Chawda]

that also you can achieve it using expression. if you want I can share

[danielnevitt]

That would be great if you can share the expression for that.

Thanks again.
Daniel

[Kushal_Chawda · Accepted solution]

the expression which I suggested earlier  you can just change the if condition values. Y to 1 wnd N to 0. then you can use suppress zero option