creating a new column in the visualization via set analysis

[bernhard_koehle]

Hi

I have a problem in the visualization by creating a new column via set analysis in a simple table.

The database contains 3 column   which are displayed in a table like:

ID	A	B
1	1	10
4	4	-40
3	4	30
2	7	20
5	55	-50
6	63	-60

Now I want to create a new column depending of the values of A and B.

A simple new column as a function via:    if (A=1 AND B=10,'YES','NO')  is working BUT very slow (depending of data).  I get 6 rows with Yes or No.  (Fine)

Because of the performance  I have to use set analysis.

Now I use:   if (    (Count(  {              ( <a={'1'} ,="" b="{'10'}">  )   }                 ID)  <> 0 )  ,'YES','NO')</a={'1'}>

But now I get only one row which contains only YES for A=1 and B=10.  All other lines are not shown.

What is my missunderstanding?

Any suggestions?

Regards

Bernhard

--

Creating the data:

Temp:

LOAD * inline [

ID,A,B

1,1,10

2,7,20

3,4,30

4,4,-40

5,55,-50

6,63,-60

] (delimiter is ',');

exit script;

[bernhard_koehle]

correction:
=if ((Count({ (<A={'1'} , B={'10'}> ) } ID) <> 0 ) ,'YES' ,'NO')
this is the used syntax. Above is a drop and drop error

[sunny_talwar]

How about this

Concat(DISTINCT {<A = {1}, B = {10}>} 'YES') &
Concat(DISTINCT {<A -= {1}>+<B -= {10}>} 'NO')

[bernhard_koehle]

Hi

Thanks for your replay.

But it is not working.

I get 6 lines and the new column is empty.

If I only use Concat(DISTINCT {<A = {1}, B = {10}>} 'YES') I only get one line. An other reason for using my kind of syntax is the count of conditions. In the real app there are up to 6 conditions in one line which are combined with up to 10 OR’sRegards

[sunny_talwar]

This seems to work

Concat({<A = {1}, B = {10}>} DUAL('YES', ID)) & Concat({<A -= {1}>+<B -= {10}>} DUAL('NO', ID))

[agigliotti]

why don't add a new field on script as below ?
ex.
Temp:
load *,
if (A=1 AND B=10,'YES','NO') as newField;
LOAD * inline [
ID,A,B
1,1,10
2,7,20
3,4,30
4,4,-40
5,55,-50
6,63,-60
] (delimiter is ',');

and use newField in UI.

[bernhard_koehle]

Hi,

yes your code is running and shows 6 rows. And in each is written YES or NO. That’s what I am looking for.

But I don’t beliefe that I can use such kind of code, because my app is much more complex and I have to support and change the conditions.

Here is real code for one row in the app (and it is not the most complex example).

For explanation:

-I only want JA = YES in the row  (the else isn’t important  // JA at the end without NO)

-the YES is only wanted if the lower (more complex conditions) is wrong

As you can see there is a lot of AND  in one line.  And the lines are combined via OR.

And at least there is an OR for the next conditions.

=if ((Count({

(

<KEIMGATTUNGART={'Escherichia|coli'}  , MBAntCode={'Piperacillin'},AntErg-={'S'}>    *  <MBAntCode={'Cefotaxim'},AntErg-={'S'}>   *  <MBAntCode={'Ciprofloxacin'},AntErg-={'S'}>

+

<KEIMGATTUNGART={'Escherichia|coli'}  , MBAntCode={'Piperacillin'},AntErg-={'S'}>    *  <MBAntCode={'Ceftazidim'},AntErg-={'S'}>  *  <MBAntCode={'Ciprofloxacin'},AntErg-={'S'}>

)

} AuftMatKnr)  <> 0 ) 

*

(

if (Count(

{

<KEIMGATTUNGART={'Escherichia|coli'} , MBAntCode={'Piperacillin'} , AntErg-={'S'}> * <MBAntCode={'Ciprofloxacin'} , AntErg-={'S'}> * <MBAntCode={'Cefotaxim'}  , AntErg-={'S'}> * <MBAntCode={'Imipenem'}   , AntErg-={'S'}>

+

<KEIMGATTUNGART={'Escherichia|coli'} , MBAntCode={'Piperacillin'} , AntErg-={'S'}> * <MBAntCode={'Ciprofloxacin'} , AntErg-={'S'}> * <MBAntCode={'Cefotaxim'}  , AntErg-={'S'}> * <MBAntCode={'Meropenem'}  , AntErg-={'S'}>

+

<KEIMGATTUNGART={'Escherichia|coli'} , MBAntCode={'Piperacillin'} , AntErg-={'S'}> * <MBAntCode={'Ciprofloxacin'} , AntErg-={'S'}> * <MBAntCode={'Ceftazidim'}  , AntErg-={'S'}> * <MBAntCode={'Imipenem'}   , AntErg-={'S'}>

+

<KEIMGATTUNGART={'Escherichia|coli'} , MBAntCode={'Piperacillin'} , AntErg-={'S'}> * <MBAntCode={'Ciprofloxacin'} , AntErg-={'S'}> * <MBAntCode={'Ceftazidim'}  , AntErg-={'S'}> * <MBAntCode={'Meropenem'}  , AntErg-={'S'}>

} AuftMatKnr) = 0 ,1,0)

)

,'JA','')

While changing the code I was doubtful if the code is correct. The row is only an optional information and don’t has to eliminate row’s. Therefore i wrote my simple example app to check.

So my question: why does set analysis  is eliminating rows in my sample-code. What is my missunderstanding? 

if ((Count({ ( <A={'1'} , B={'10'}> ) } ID)  <> 0 ) ,'JA','NEIN') 

Regrads

[bernhard_koehle]

If the else is to slow. And i have to interact with the filters in the app. So i have to do it in the visualisation.

[sunny_talwar]

So my question: why does set analysis  is eliminating rows in my sample-code. What is my missunderstanding? 
if ((Count({ ( <A={'1'} , B={'10'}> ) } ID)  <> 0 ) ,'JA','NEIN') 

Because that is what the set analysis do... it will show value based on your condition and would totally hide things which are out of set analysis's selection.... may be try this

If(Count({< A = {'1'}, B = {'10'}>} ID) + Sum(0) <> 0, 'JA', 'NEIN')