topic Re: Assign value if filled cell otherwise none in QlikView

Assign value if filled cell otherwise none

Wed, 08 Apr 2015 09:24:30 GMT

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.

Abbreviation	Value	Header 3	Header 4	Header 5
AAA	TRS20140202	x	x	x
B	EGM20140312		x	x
A	20140606	x	x	x
AA		x		x
BBB	EGM20140311		x	x
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!

Re: Assign value if filled cell otherwise none

Anonymous — Wed, 08 Apr 2015 09:28:23 GMT

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

Re: Assign value if filled cell otherwise none

Anonymous — Wed, 08 Apr 2015 09:29:18 GMT

you can write something

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

same as for header 3 just change the if condition

Thanks

BKC

Re: Assign value if filled cell otherwise none

Anonymous — Wed, 08 Apr 2015 09:31:16 GMT

you can also use it in chart expression

Re: Assign value if filled cell otherwise none

morganaaron — Wed, 08 Apr 2015 09:35:28 GMT

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.

Re: Assign value if filled cell otherwise none

Wed, 08 Apr 2015 09:36:01 GMT

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