Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bernhard_koehle
Partner
Partner

creating a new column in the visualization via set analysis

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;

Labels (3)
8 Replies
bernhard_koehle
Partner
Partner
Author

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
Partner
Partner
Author

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
Partner
Partner

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
Partner
Partner
Author

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
Partner
Partner
Author

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