I need to remove data from dimension and fact table which is not required. I tried both keep and exists functions but somehow couldn't figure out how to eliminate the rows i don't want.
I also attached qvw and see if you can help me in this.
You want to remove unwanted data from Data model or in just front end?
If it is front end create a straight table and add Workorder1 and Name as dimensions and Sum(pay) as expression.
don't forget to check Suppress when Value is Null in Name Dimension
Tick ✓ to 'Omit if value is Null' option for coloumn 'Name' if its a tablebox (Properties -> Presentation Tab).
Else if its a Pivot Table/Straight Table - Tick ✓ to 'suppress when value is null' option.
Hope this will resolve your problem.
Prashant P Baste
1. Change to a Straight Table Dimension = Workorder1 and Name
2. Flag Name Dim - 'Suppress When When is Null'
3. Set the Expression = Sum(pay)
4. Presentation tab - Suppress Zero values and Suppress Missing both ticked
HHT - Andy
See the attached
Try this script:
LOAD * inline
LOAD * inline
Load Right(Workorder,1) as Workorder,
Sum(Pay) as Pay
Where Workorder <> 'a4'
Group By Workorder;
DROP Table GL;
Load Workorder as Workorder2,
Where Exists(Workorder1, Workorder);
DROP Table GL1;
LOAD Workorder1 as Workorder2,
Where Exists (Workorder2, Workorder1);
DROP Table discipline;