Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielnevitt
Creator
Creator

Load flag field

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

2 Solutions

Accepted Solutions
Kushal_Chawda

try below

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

 

Annotation 2020-08-05 114009.png

View solution in original post

Kushal_Chawda

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 

View solution in original post

9 Replies
Anonymous
Not applicable

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

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

try below

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

 

Annotation 2020-08-05 114009.png

danielnevitt
Creator
Creator
Author

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

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

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

Thanks again.
Daniel

Kushal_Chawda

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