Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Assign value if filled cell otherwise none

Hi,

I have a problem which I cannot solve by myself. I need to assign a character ('x') in 'header 3' if the first field contains an abbreviation that is A, AA or AAA. (I know I can do this with left join, but I need to do it in combination with the upcoming problem). In addition, I need to assign a character ('x') to 'header 4' if the field 'Value' is not empty. There is no way for me to write all names in a script, because there are in reality 1 million rows with new names added continuously. This is just an example. Below you can see this illustrated.

AbbreviationValueHeader 3Header 4Header 5
AAATRS20140202xxx
BEGM20140312xx
A20140606xxx
AAxx
BBBEGM20140311xx
BB

In this case, if the first cell contains A, AA or AAA, 'x' is displayed in header 3. However, I also want 'x' to be displayed in header 4 if the field Value contains something (i.e., not empty).

But my purpose here is actually to get header 5. If either of the first two fields or both fields meet the above mentioned criteria, I want something to be displayed. In this case 'x'. Otherwise the cell should remain empty.

Thank you in advance!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

for header 4 use

if len(Value)>0,'X')

for header 5

if (left(Abbreviation,1)=A) and (len(Value)>0),'X'

hope that helps

View solution in original post

5 Replies
Anonymous
Not applicable
Author

for header 4 use

if len(Value)>0,'X')

for header 5

if (left(Abbreviation,1)=A) and (len(Value)>0),'X'

hope that helps

Anonymous
Not applicable
Author

you can write something

if (len(Abbreviation)>0,'X',' ') as [Header 5]

same as for header 3 just change the if condition

Thanks

BKC

Anonymous
Not applicable
Author

you can also use it in chart expression

morganaaron
Specialist
Specialist

Add the conditions as a preceding load statement.

If you have millions of values and you only want A, AA or AAA, I'd use:

If(Match(Abbreviation, 'A', 'AA', 'AAA'), 'x',) as Header3

to ensure you don't pick up any other values that could start with an A etc.

As stated in the other answers, your:

If(Len(Value)>0, 'x',) as Header4

should work. Combine them both in an if(... or...) statement to get Header5.

Not applicable
Author

Thanks Rudolf and balkumarchandel! Both of your answers helped me get the correct output!