Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
you can write something
if (len(Abbreviation)>0,'X',' ') as [Header 5]
same as for header 3 just change the if condition
Thanks
BKC
you can also use it in chart expression
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.
Thanks Rudolf and balkumarchandel! Both of your answers helped me get the correct output!