Remove row selection from dataset

[Kirsten]

I would like to remove a row selection in my complete dataset. So e.g. in the column Category, I want to remove FCO but keep Warranty. Though I found an expression, it is not working as expected. 

LOAD
Category<> 'FCO',

What it does it converts my warranty values to -1 and FCO to 0. But the FCO values, now converted to 0, still exist in  my dataset

See attachment

[tresesco]

Your '<>' comparison should be in Where clause, like:

Load

          Category

From <source path> Where  Category<> 'FCO';

[Kirsten]

Thanks @tresesco  But how to construct that in the complete code. See attachment (and previous attachment)

This goes wrong

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
Where Category<> 'FCO';
(ooxml, embedded labels, table is Sheet1);

And this goes wrong as well:

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1);
Where Category<> 'FCO';

[nikitadeshwal · Accepted solution]

Kirsten,

Write like this,

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1) Where Category<> 'FCO';

[Kirsten]

Thanks that works! @nikitadeshwal 

And what if you want to exclude multiple rows from additional columns (Bu = column, doseaware= row in BU, hemo = row in BU)

These both don't work (see attachment)

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1) Where Category<> 'FCO', Bu <> 'DoseAware', 'Hemo;

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1) Where Category<> 'FCO', Where Bu <> 'DoseAware', 'Hemo;

[tresesco]

Try like:

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1) Where Category<> 'FCO'  OR  Not Match(Bu,'DoseAware', 'Hemo');

Note : You can use AND instead of OR if you need.

[nikitadeshwal · Accepted solution]

Kirsten,

Use below,

FROM [lib://FP&A CoNQ IGT_PPG_BU_Product Desc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1) where Category<>'FCO' and not match(Bu,'DoseAware', 'Hemo');

Regards,

Nikita Deshwal

[Kirsten]

Works! Thanks very valuable and appreciated.

[Kirsten]

@nikitadeshwal 

I would like to now filter a group in my dataset. E.g. When I say  = FCO that I only see the FCO rows  and not Warranty.  I would like to do this because sometimes I need to select rows with 0 values only, and all other 'not 0 values'-rows have 10.000 different productnames, which is impossible to exclude all individually with the exclude selection.

I thought the expression should look like this. Though when I view the barcharts of the data, I see that I did not make a selection of the desired FCO row and BU (see bold and attachment)

FROM [lib://CoNQ _IGT_2019_BU_PPG_Productdesc (code1_310155074)]
(ooxml, embedded labels, table is Sheet1) Where Category= 'FCO' OR Match(Bu,'IGT-S MoS')
AND Not Match(PPG,'GTC-COMPONENT', 'HEMO', 'MOBILE') AND Not Match (Type, 'labor only, no parts', 'Tubes')
AND Not Match("# of Parts", '1') AND Not Match("Case Type", 'FC', 'DS', 'PM');