topic Re: creating a new column in the visualization via set analysis in App Development

creating a new column in the visualization via set analysis

bernhard_koehle — Sat, 16 Nov 2024 05:08:33 GMT

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;

Re: creating a new column in the visualization via set analysis

bernhard_koehle — Mon, 29 Jul 2019 12:23:39 GMT

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

Re: creating a new column in the visualization via set analysis

sunny_talwar — Mon, 29 Jul 2019 12:28:25 GMT

How about this

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

Re: creating a new column in the visualization via set analysis

bernhard_koehle — Mon, 29 Jul 2019 12:49:28 GMT

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

Re: creating a new column in the visualization via set analysis

sunny_talwar — Mon, 29 Jul 2019 13:16:24 GMT

This seems to work

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

Re: creating a new column in the visualization via set analysis

agigliotti — Mon, 29 Jul 2019 13:25:53 GMT

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.

Re: creating a new column in the visualization via set analysis

bernhard_koehle — Tue, 30 Jul 2019 05:38:48 GMT

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={'Ceftazidim'} , AntErg-={'S'}> * <MBAntCode={'Imipenem'} , 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

Re: creating a new column in the visualization via set analysis

bernhard_koehle — Tue, 30 Jul 2019 05:40:44 GMT

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.

Re: creating a new column in the visualization via set analysis

sunny_talwar — Tue, 30 Jul 2019 18:36:29 GMT

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')