Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have created pivot table with one dimension iin column and one dimension in row and another measure.
I have null values for few records as below. we should exclude reocrds with null values at measure
report is like below
D1 D2 D3
Cus1 100 - -
Cus2 - 20 -
Cus3 10 - -
Cus4 - - -
Cus5 10 - 20
We should exclude nul values records where we have 2 nulls or 3 nulls. We can keep record with only one null value
Expected
D1 D2 D3
Cus5 10 - 20
Hi,
you can try this expression:
SUM({<CustID={"=RANGENULLCOUNT(D1,D2,D3)<2"}>} D1) in D1 expression column and so on until D3/Dn
Hi! Try this:
Data:
CROSSTABLE (Dimension, Value)
LOAD
Customer,
D1,
D2,
D3
INLINE [
Customer,D1,D2,D3
Cus1,100,,
Cus2,,20,
Cus3,10,,
Cus4,,,
Cus5,10,,20
];
Temp_A:
LOAD
Customer AS Temp_Customer,
Dimension AS Temp_Dimension,
IF(LEN(TRIM(Value))=0, 0, 1) AS Value_Count
RESIDENT Data;
Temp_B:
LEFT JOIN (Data) LOAD
Temp_Customer AS Customer,
Sum(Value_Count) AS Value_Count
RESIDENT Temp_A
GROUP BY Temp_Customer;
DROP TABLE Temp_A;
Final:
LOAD
Customer,
Dimension,
Value
RESIDENT Data
WHERE Value_Count >= 2;
DROP TABLE Data;
i have large data, i cannot use data in script
Can you as how to do with expression at report
Hi
I have created pivot table with one dimension iin column and one dimension in row and another measure.
I have null values for few records as below. we should exclude reocrds with null values at measure
report is like below
D1,D2,D3 are values for column in columns section
Cus1,Cus2 are values for column in row section
100,20,- are values in measures section
D1 D2 D3
Cus1 100 - -
Cus2 - 20 -
Cus3 10 - -
Cus4 - - -
Cus5 10 - 20
We should exclude nul values records where we have 2 nulls or 3 nulls. We can keep record with only one null value
Expected
D1 D2 D3
Cus5 10 - 20
Try this:
1) Go to Add-ons -> Data handling and uncheck "Include zero values"
2) Use this expression in your pivot table:
If(Aggr(Count(Value),Customer)<2, Null(), Value)
i tried thsi laready, but no luck
1) Go to Add-ons -> Data handling and uncheck "Include zero values"
2) Use this expression in your pivot table:
Hi Nagamani,
Sample Script:
CrossTable(Group,Value)
load * Inline [
Cust,D1,D2,D3
Cus1,100,,
Cus2,,20,
Cus3,10,,
Cus4,,,
Cus5,10,,20
];
Try this expression:
=IF(AGGR(count({<Value={"=len(Value)>0"}>}Group),Cust)>1,Cust)