Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
hrlinder
Honored Contributor

Re: Assign value if filled cell otherwise none

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

5 Replies
hrlinder
Honored Contributor

Re: Assign value if filled cell otherwise none

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

balkumarchandel
Valued Contributor II

Re: Assign value if filled cell otherwise none

you can write something

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

same as for header 3 just change the if condition

Thanks

BKC

balkumarchandel
Valued Contributor II

Re: Assign value if filled cell otherwise none

you can also use it in chart expression

morganaaron
Valued Contributor

Re: Assign value if filled cell otherwise none

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

Re: Assign value if filled cell otherwise none

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

Community Browser