Conditional Filter

[harithareddy]

Hello Community,

I have a table,

Table:
LOAD * INLINE [
Type, Field, IDs
A, S, 1
A, S, 3
A, S, 2
A, S, 5
A, , 56
A, , 34
B, S, 57
B, S, 58
B, S, 59
B, S, 23
B, , 46
];

Table1:
Load *,

IF(Type='A'and Field='S','C',Type) as New_Type

Resident Table;

Drop table Table;

I need to create a List box on Type column in out put i need to get as A,B,C the condition for c,When Type = 'A' and Field='S' and i have a pie chart in dashboard which will show the count of ID's ,When i click the the type A in new_type filter the count is showing as 2( it is showing the records for which the type is A and  fiels is not 'S')but i need to get 6(all the records for the type A).

PLease find the attchment for the same.

Thanks in advance for the help 

[Gysbert_Wassenaar]

It sounds like you want a value to be two values at once. New_Type should be A because Type is A and New_Type should also be C because Type is A and Field is S. That simple won't work in this universe. It's either one or the other, never both at the same time.

[harithareddy]

Thanks for Quick reply,
Type C is a subset of A.
A contains the records for both field S and null but C should show the records only for the field as S.
I have a built 3 piviot table based on the types now my requirement is to show a chart for all the types user will select the type and check the count

[Gysbert_Wassenaar · Accepted solution]

Try creating a new table with a new field that links to the IDs in Table1.

Table:
LOAD * INLINE [
Type, Field, IDs
A, S, 1
A, S, 3
A, S, 2
A, S, 5
A, , 56
A, , 34
B, S, 57
B, S, 58
B, S, 59
B, S, 23
B, , 46
];

Table1:
Load *,

IF(Type='A'and Field='S','C',Type) as New_Type

Resident Table;

Drop table Table;

NewerType:
LOAD DISTINCT
    Type as Newer_Type,
    IDs
RESIDENT Table1;

CONCATENATE (Table1)

LOAD DISTINCT
    New_Type as Newer_Type,
    IDs
RESIDENT Table1;

[harithareddy]

Thanks A lot It worked