topic Re: Create Flag in QlikView

Create Flag

qlikviewaf — Wed, 12 May 2021 13:05:30 GMT

Hi,

i have a data-set as follow. I would like to implement in the loading script a FLAG that should be equal to Y if on the order_number is contained the item number=AAAA.

Which could be best way to do this?
THanks!

Re: Create Flag

MayilVahanan — Thu, 13 May 2021 01:12:39 GMT

HI @qlikviewaf

Try like below

Table1:
LOAD Region, OrderNumber, Item, OrderType, Amount from ursourcetable;

Join
Load OrderNumber, 'Y' as Flag Resident Table1 Where Item = 'AAAA';

Hope it helps

Re: Create Flag

Saravanan_Desingh — Thu, 13 May 2021 01:51:15 GMT

Try similar to this,

https://community.qlik.com/t5/QlikView-App-Dev/Pivot-extract-only-certain-orders/td-p/1807197

Re: Create Flag

qlikviewaf — Thu, 13 May 2021 07:45:55 GMT

What if for the same order i got 2 item AAAA listed ?

I guess that the join below, needs to be adjusted in order to load only first row found, avoiding duplicates then. It make sense ? how can this be done

Join
Load OrderNumber, 'Y' as Flag Resident Table1 Where Item = 'AAAA';

Thanks!

Re: Create Flag

mato32188 — Thu, 13 May 2021 07:52:05 GMT

Hi @qlikviewaf ,

I would choose ApplyMap approach, iow fast lookup. In script editor do the following:

MAP: //select order numbers where item = AAAA

mapping load distinct

OrderNumber,

'Y'

from XXX

where Item = 'AAAA';

final table:

Load *,

ApplyMap('MAP',OrderNumber,'N') as Flag

From XXX;

Re: Create Flag

MayilVahanan — Fri, 14 May 2021 01:00:15 GMT

HI @qlikviewaf

You can use distinct load

Join
Load distinct OrderNumber, 'Y' as Flag Resident Table1 Where Item = 'AAAA';

you can use Applymap as suggested by @mato32188