Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Neha121
Contributor III
Contributor III

Comparing the dimension value with below row


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

 

 

Labels (5)
1 Solution

Accepted Solutions
pallavi_96
Partner - Contributor III
Partner - Contributor III

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

View solution in original post

3 Replies
pallavi_96
Partner - Contributor III
Partner - Contributor III

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
Creator
Creator

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
Contributor III
Contributor III
Author

Thanks @pallavi_96 this works

PREVIEW