topic Re: Load flag field in QlikView

Load flag field

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Load flag field

Anonymous — Wed, 05 Aug 2020 08:49:48 GMT

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;

Re: Load flag field

danielnevitt — Wed, 05 Aug 2020 09:18:45 GMT

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

Re: Load flag field

Kushal_Chawda — Wed, 05 Aug 2020 10:42:12 GMT

try below

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

Re: Load flag field

danielnevitt — Wed, 05 Aug 2020 16:46:50 GMT

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

Re: Load flag field

Kushal_Chawda — Wed, 05 Aug 2020 17:40:57 GMT

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

Re: Load flag field

danielnevitt — Wed, 05 Aug 2020 19:05:01 GMT

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

Re: Load flag field

Kushal_Chawda — Wed, 05 Aug 2020 19:07:59 GMT

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

Re: Load flag field

danielnevitt — Wed, 05 Aug 2020 19:11:33 GMT

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

Thanks again.
Daniel

Re: Load flag field

Kushal_Chawda — Wed, 05 Aug 2020 19:26:15 GMT

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