Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
Kush
MVP
MVP

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

Kush
MVP
MVP

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

Kush
MVP
MVP

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

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

Kush
MVP
MVP

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

Kush
MVP
MVP

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

Kush
MVP
MVP

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