Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Have table with three columns
NAME | VALUE1 | VALUE2 |
AAA | 1;2;3 | 1;3 |
BBB | 2;3;4 | 4;5 |
CCC | 11;12;13;14;15 | 13;15 |
DDD | 21;22;23;24 | 31;32 |
EEE | 41;42;43;45;46;47 | 43;47 |
So, my goal is to know which NAMES has values from VALUE1 that also appear in VALUE2.
output
NAME | VALUE1 | VALUE2 | flag |
AAA | 1;2;3 | 1;3 | yes |
BBB | 2;3;4 | 4;5 | no |
CCC | 11;12;13;14;15 | 13;15 | yes |
DDD | 21;22;23;24 | 23;32 | no |
EEE | 41;42;43;45;46;47 | 43;47 | yes |
Check this may be -
source:
Load NAME,VALUE1, VALUE2, if(SubStringCount(FLAG,'NO')>=1, 'NO', 'YES') as YNFLAG
;
Load NAME,VALUE1, VALUE2, Concat(FLAG1,';') as FLAG
Group By NAME,VALUE1, VALUE2
;
Load *, If(SubStringCount(VALUE1, VALUE2_ITEMS)>=1,'YES','NO') as FLAG1
;
Load *, subfield(VALUE2,';') as VALUE2_ITEMS
inline [
NAME, VALUE1, VALUE2
AAA, 1;2;3, 1;3
BBB, 2;3;4, 4;5
CCC, 11;12;13;14;15, 13;15
DDD, 21;22;23;24, 31;32
EEE, 41;42;43;45;46;47, 43;47
]
Check this may be -
source:
Load NAME,VALUE1, VALUE2, if(SubStringCount(FLAG,'NO')>=1, 'NO', 'YES') as YNFLAG
;
Load NAME,VALUE1, VALUE2, Concat(FLAG1,';') as FLAG
Group By NAME,VALUE1, VALUE2
;
Load *, If(SubStringCount(VALUE1, VALUE2_ITEMS)>=1,'YES','NO') as FLAG1
;
Load *, subfield(VALUE2,';') as VALUE2_ITEMS
inline [
NAME, VALUE1, VALUE2
AAA, 1;2;3, 1;3
BBB, 2;3;4, 4;5
CCC, 11;12;13;14;15, 13;15
DDD, 21;22;23;24, 31;32
EEE, 41;42;43;45;46;47, 43;47
]
Thank you so much