topic Re: Search for a value from multiple rows and create a new column whether the value exists in QlikView

Search for a value from multiple rows and create a new column whether the value exists

Qlik_Learner11 — Sat, 30 Sep 2023 21:07:03 GMT

Hi,

I need to search for a value in multiple rows and if the value exists in any of the rows, I need to flag it. Below table has columns case# and remark. I need to search value "x" in remark, if it exists in any of the rows for a case#, I need to flag it. The value "x" could be any of the rows for a case#.

Sample:

Output required:

I do NOT need this. Using If statement gives me below output.

case#	remark_x
1	N
1	N
1	Y
1	N
2	N
2	N
2	N
3	Y
3	N

Thank you!

Re: Search for a value from multiple rows and create a new column whether the value exists

MayilVahanan — Tue, 03 Oct 2023 06:56:44 GMT

Try like below

If you have only case# as dim, then exp is

if(SubStringCount(Concat(DISTINCT remark, ','), 'x') > 0, 'Y', 'N')

suppose, if you have both case#, remark as dim, then exp is

if(SubStringCount(Concat(DISTINCT TOTAL<case#> remark, ','), 'x') > 0, 'Y', 'N')

Re: Search for a value from multiple rows and create a new column whether the value exists

Qlik_Learner11 — Sat, 07 Oct 2023 11:54:29 GMT

Thanks so much!