Comparing the dimension value with below row

[Neha121]

Hi all,

     If I have source table as below.. I want to compare meets hurdle column and if for Type T1 and type T2 if meets hurdler is yes then need to have eligible column as Yes else no as below in target table. Want to create a new target table in script where key column links to key that links other tables..
 
 How do I do that.. 

source table
Key   Type    Meets hurdle

k1      T1       Yes

k1      T2       No

k2      T1      Yes

k2      T2      Yes

target table

key   eligible

k1    No

k2   Yes

[pallavi_96 · Accepted solution]

Hi @Neha121 ,

you can try the below logic to get your expected output

TargetTable:
LOAD
Key,
If(
Sum(If(Type = 'T1' and MeetsHurdle = 'Yes', 1, 0)) > 0
and Sum(If(Type = 'T2' and MeetsHurdle = 'Yes', 1, 0)) > 0,
'Yes',
'No'
) as Eligible
RESIDENT SourceTable
GROUP BY Key;

Please check and let me know if it works for you

[Padma123]

Sample:

load * Inline [
key ,type,Meets hurdle
K1,T1,Yes
K1,T1,No
K2,T1,Yes
K2,T2,Yes
]
where [Meets hurdle]='Yes';

target:
load key,If(count=1,'No','Yes') as eligible;
load key,Count([Meets hurdle]) as count
Resident t1
group by key;

[Neha121]

Thanks @pallavi_96 this works

PREVIEW