FLAG creation help in load script

[jarokall]

Hello,

i'm trying to create FLAG in load script but nothing works. I need to flag 4 cases - EXISTS, NOT EXISTS, EXISTS ONLY PISRO, EXISTS ONLY PILTD. 

If 'component_filter_full' exists in 'product_filter_full' and in 'site_full' exists two values PISRO and PILTD then flag=EXISTS

If 'component_filter_full' exists in 'product_filter_full' and in 'site_full' exists only one value PISRO then flag=EXISTS ONLY PISRO

If 'component_filter_full' exists in 'product_filter_full' and in 'site_full' exists only one value PILTD then flag=EXISTS ONLY PILTD

If 'component_filter_full' NOT exists in 'product_filter_full' then flag=NOT EXISTS

Can anyone help me with the condition please? I need to do it in load script for filter by FLAG in app.

Thank You

[Anil_Babu_Samineni]

Perhaps this

If(Exists(component_filter_full,product_filter_full) and Exists(component_filter_full, Match(site_full,'PISRO','PILTD')), 'EXISTS',
If(Exists(component_filter_full,product_filter_full) and Exists(component_filter_full, Match(site_full,'PISRO')), 'EXISTS ONLY PISRO',
If(Exists(component_filter_full,product_filter_full) and Exists(component_filter_full, Match(site_full,'PILTD')), 'EXISTS ONLY PILTD',
If(Not Exists(component_filter_full,product_filter_full), 'NOT EXISTS')))) as Flag

[Vegar]

What about something like this?

If(ITMREF_0<>CPWITMREF_0, 'NOT EXISTS', Pick(MATCH(STOFCY_0,'PISRO', 'PILTD', 'PISRO', 'PILTD'), 'EXISTS', 'EXISTS', 'EXISTS ONLY PISRO', EXISTS ONLY PILTD'))  As Flag

[rubenmarin]

Hi, I think you can use a temporary table that sets for each 'product_filter_full' the flag it has based on 'site_full' and retrieve the flag using a mapping table.

tmpFlag:
LOAD product_filter_full,
  If(Index(sites, 'PISRO') and Index(sites, 'PILTD'), 'EXISTS',
    If(Index(sites, 'PISRO'), 'EXISTS ONLY PISRO',
        If(Index(sites, 'PILTD'), 'EXISTS ONLY PILTD'))) as Flag
;
LOAD product_filter_full, ';' &Concat(site_full,';')&';' as sites
Resident/From...
Group by product_filter_full;

mapFlag:
Mapping LOAD product_filter_full, Flag Resident tmpFlag;

DROP table tmpFlag;

Data:
LOAD..., 
  component_filter_full,
  applymap('mapFlag',component_filter_full,'NOT EXISTS') as Flag
Resident/From...