Sum of count distinct value & exception some value

[sivavasam]

Hi , I have table content with room number(A) , num of persons(B) , extra person(c) ,extra charge(D) , adults/ child(E) columns. In this if extra person is child there will be no extra charge equal to 0 and 0 charg for entire room mem. I need sum of distinct extra charge column.

A    B   C      D        E 

20   3   1   100      Adult 

20   3   1   100     Adult

20   3    1   100     Child 

30    3   1   100    Adult

30    3    1   100    Adult

30    3     1   100    Adult

Total           200 

But I need result following 

A    B   C      D        E 

20   3   1    0      Adult 

20   3   1    0     Adult

20   3    1    0     Child 

30    3   1   100    Adult

30    3    1   100    Adult

30    3     1   100    Adult

Total           100 

[Aasir]

You can create a new column using Qlik's script editor to achieve this. 

YourTableName:
LOAD
A,
B,
C,
D,
E,
If(E = 'Child', 0, D) as NewExtraCharge
FROM [Your Data Source];

NewTableName:
LOAD
A,
B,
C,
Sum(NewExtraCharge) as SumDistinctExtraCharge
Resident YourTable
Group By A, B, C, E;

DROP Table YourTableName;

[sivavasam]

I need the solution in chart expression level , could you pls help me with that ? 

Room number contains adult and child , if child is there charges zero ...I need to present zero value for adult with in the same room number 

[MayilVahanan]

Hi ,

Try like below

Dim: A, B, C, E

Exp: If(SubStringCount(Concat(Distinct Total<A> E, ','), 'Child')=1, 0, D)

[Aasir]

Try this in Chart level
Assuming you have a chart where you want to show the sum of distinct extra charges

Sum({<E={'Adult'}>} D) + Sum({<E={'Child'}>} 0)

Replace 'D' and 'E' with the actual column names representing extra charges and adults/child.

[sivavasam · Accepted solution]

Thanks for all ....but ...I didn't get the solution as suggested.

But I have got solution by myself.

• Have created another column with child charges 100 with respect to Room number and then I have subracted new column from D column....then I got result ....