calculated measure in script

[Qliksense_77]

Hi,

I have data like below  except the red text. 

The red text is a calculation that I want to do in the script. The result should be a calculated measure named "Value2".

The purpose is that I can display "Value2" in visualisations without the other fields.

Or maybe there are better ways to display value2 in graphs and so on? Maybe use aggr with the expression?

@OmarBenSale helped me with figure out the expression in an earlier post. 

The purpose of the expression: When id and dates are identical, and the names starts with "b*" and "c*", then  only sum rows where name starts with "c". 

Id	Date	Name	Value	if(index(concat (distinct total <Id,Date> Name),'b*')>0 and index(concat (distinct total <Id,Date> Name),'c*')>0,sum({<Name={'c*'}>}Value),sum(Value))
1	10-10-2020	a*	1	1
2	11-10-2020	b*	2	2
3	12-10-2020	b*	3	0
3	12-10-2020	c*	4	4
4	13-10-2020	a*	5	5
4	13-10-2020	b*	6	6

[rubenmarin · Accepted solution]

Hi, to do this in script you can check wich ids has a 'c' and 'b':

chkIdDate:
LOAD chkIdDate
Where Count=2;
LOAD chkIdDate,
 count(distinct Name) as Count
group by chkIdDate;
LOAD Id &'_'&Date as chkIdDate,
  Left(Name,1) as Name
Resident/From...
Where Left(Name,1)='b' or Left(Name,1)='c';

Then you can load data and create this field using this check:

Data:
LOAD
  Id,
  Date
  ...
  If(Exists('chkIdDate', Id&'_'&Date) and Left(Name,1)<>'c'), 0, Value) as Value2
From/Resident...

[Qliksense_77]

Hi @rubenmarin ,

It works perfect with the simplified data in my post. 

I do have a hard time getting in to work in my real datamodel where I need the solution for multiple rules and the fields are from different sources.

I have expanded the expression from my first post and it sort of works. But it only works in a straight table and when the fields ID and Date are added to the table. And also for some reason it counts -1 instead of 1??

Can I somehow use this expression for a count of the total in the frontend (use in graphs and other visualisation) ? 

The expression is like below. I cant make up so many different colours, so i have used numbers 🙂

=(if(index(concat (distinct total <,date> Colour),'1')>0 and

(index(concat (distinct total <,date> Colour),'2')>0

or index(concat (distinct total <,date> Colour),'3')>0

or index(concat (distinct total <,date> Colour),'4')>0

or index(concat (distinct total <,date> Colour),'5')>0

or index(concat (distinct total <,date> Colour),'6')>0

or index(concat (distinct total <,date> Colour),'7')>0

or index(concat (distinct total <,date> Colour),'8')>0

or index(concat (distinct total <,date> Colour),'9')>0

or index(concat (distinct total <,date> Colour),'10')>0

or index(concat (distinct total <,date> Colour),'11')>0

or index(concat (distinct total <,date> Colour),'12')>0

or index(concat (distinct total <,date> Colour),'13')>0

or index(concat (distinct total <,date> Colour),'14')>0

or index(concat (distinct total <,date> Colour),'15')>0

or index(concat (distinct total <,date> Colour),'16')>0

or index(concat (distinct total <,date> Colour),'17')>0

or index(concat (distinct total <,date> Colour),'18')>0

or index(concat (distinct total <,date> Colour),'19')>0

or index(concat (distinct total <,date> Colour),'20')>0

or index(concat (distinct total <,date> Colour),'21')>0),

sum({<Colour={'2','3','4',

'5',

'6',

'7',

'8',

'9',

'10',

'11',

'12',

'13',

'14',

'15',

'16',

'17',

'18',

'19',

'20',

'21',

}>}value),sum(value))

and

if(index(concat (distinct total <,date> Colour),'22')>0 and

index(concat (distinct total <,date> Colour),'23')>0,

sum({<Colour={'23'}>}value),sum(value))

and

if(index(concat (distinct total <,date> Colour),'24')>0 and

index(concat (distinct total <,date> Colour),'23')>0,

sum({<Colour={'23'}>}value),sum(value))