topic Re: Create column based on multiple conditions WITHOUT nested if in QlikView

Create column based on multiple conditions WITHOUT nested if

NataliaD — Fri, 09 Oct 2020 09:29:32 GMT

Hello,

I want to create a column named Status having Character Values in a straight table based on other fields, i.e.

if column A = 'a' return Status = 'a',
if not then check column B, if column B = 'b' return Status = 'b',
if not then check column C, if column C = 'c' return Status = 'c',
if not return Status = 'w'

I've also attached an image explaining this.

The problem is that I do NOT want to use nested If, because it takes too long to execute, so I try to find a way using Set Analysis, but I cannot understand how.

Thank you!

Re: Create column based on multiple conditions WITHOUT nested if

Taoufiq_Zarra — Fri, 09 Oct 2020 09:58:22 GMT

@NataliaD May be this help :

Dimension: A,B,C

Expression:

=pick(Match(left(A&B&C,1),'','a','b','c'),'w','a','b','c')

output:

Re: Create column based on multiple conditions WITHOUT nested if

NataliaD — Fri, 09 Oct 2020 11:17:34 GMT

Thank you, I'll try it! But I have another problem that I forgot to mention. Column C has two values so how can I write them both ? because you wrote 'c' but there are two values I want to check..

Re: Create column based on multiple conditions WITHOUT nested if

Kushal_Chawda — Fri, 09 Oct 2020 11:35:45 GMT

@NataliaD try below. Where a,b and c are your 3 fieldnames

if(len(trim(subfield(a&'_'&b&'_'&c,'_',1)))>0,subfield(a&'_'&b&'_'&c,'_',1), if(len(trim(subfield(a&'_'&b&'_'&c,'_',2)))>0,subfield(a&'_'&b&'_'&c,'_',2), if(len(trim(subfield(a&'_'&b&'_'&c,'_',3)))>0,subfield(a&'_'&b&'_'&c,'_',3),'W'))) as TEST

Re: Create column based on multiple conditions WITHOUT nested if

Brett_Bleess — Tue, 27 Oct 2020 13:31:16 GMT

@NataliaD be sure to remember to return to the thread to close things out if one of the two suggestions helped by using the Accept as Solution button on the post(s) that helped, you can mark more than one post, just FYI. If you did something different, you can post what you did and then mark that post too. It will be greatly appreciated if you can close the thread if you have figured out a solution.

Regards,
Brett