how to accomplish this

[ali_hijazi]

Hello 
I got the following sample data:

CompanyCode    JournalNo.   ledgerType    entryType     Amount

010                     12345           4                    ttt___            699
010                     12345           2                    FSSA            599
010                     678               4                    GRP___       700
010                     5544             2                    GFD              400

I want to display in a stright table the journal numbers whose ledger type is not 4

the expected result is 

JournalNo.          Amount
12345                 599
5544                   400
678                     700

I created the following calculated dimension

aggr(only {<LedgerType ={4}, EntryType={GRP___}> + <LedgerType -={4}>}JournalNo.

I'm getting the following result

JournalNo.          Amount
12345                 699
12345                 599
5544                   400
678                     700

how can i exclude the line in blue?
I know it appeared because the journalNo. satisfies the condition LedgerType -= 4

[æden]

It works as below:

[ali_hijazi]

actually I got a straight table with a lot of columns (25 columns)
I put the condition on the JournalNo. column I won't repeat the same condition on all columns

[Vegar]

@ali_hijazi 

Your expression should work if you make sure that the field names used in the set expression matches the field names found in the data model and that your overall syntax is correct. I found a few issues with the expression you posted when comparing to the table you presented. 

Consider this data table:

LOAD *
INLINE[
CompanyCode,JournalNo.,LedgerType ,EntryType,Amount
010,12345,4,ttt___,699
010,12345,2,FSSA,599
010,678,4,GRP___,700
010,5544 ,2,GFD,400
];

Then both of these expressions will work:

=aggr(only({<LedgerType ={4}, EntryType={GRP___}> + <LedgerType -={4}>}Amount), JournalNo.)

=only({<LedgerType ={4}, EntryType={GRP___}> + <LedgerType -={4}>}Amount)

[alejandroquinones · Accepted solution]

Hi @ali_hijazi ,

From your previous messages, I understand you do not want to repeat the set expression across all of your measures.

I couldn't achieve this using your formula, as it seems to only apply the set expression to the dimension. For the value JournalNo. = 12345, two records are possible and the sum(Amount) is incorrect:

This is happening because you are aggregating over JournalNo, and there are multiple records for the value '12345'. To solve this problem, you can use RowNo() (field ID) to generate a new field to act as the surrogate key for that table, and then aggregate over this new field. Be sure to exclude null values from the calculated dimension to avoid displaying them in your results. However, users filtering by this value will not know what the ID represents.

=Aggr(Only({<LedgerType={4}, EntryType={'GRP___'}> + <LedgerType-={4}>} [JournalNo.]),  [JournalNo.], [ID])

2nd method: Using an If() statement, as it evaluates each row independently. I just had to remove the null values from the calculated dimension (otherwise you'll see a null value):

=If((LedgerType=4 AND EntryType='GRP___') OR (LedgerType<>4), JournalNo. )

To prevent the formula from appearing in the selection pane when a user selects a JournalNo in the table, create a master dimension and name it JournalNo.

Regards,

Alex

[Qrishna]

if Aggr(only(..)) is not working for you, you can try using below calculated dim expression:

=if(Not (Match(ledgerType, 4) and Match(entryType, 'ttt___')), ledgerType)

and uncheck the box 'Suppress when value is null'/'include null values'

[hanna_choi]

@ali_hijazi 

Do you have to use expressions on the visualization screen?
How about using modeling to remove that row?

Condition: If the JournalNo value is duplicated, remove the row where the ledgerType value is 4

**********************************

Temp_Data:
Load
*
inline [
CompanyCode, JournalNo, ledgerType, entryType, Amount
010, 12345, 4, ttt___, 699
010, 12345, 2, FSSA, 599
010, 678, 4, GRP___, 700
010, 5544 , 2,GFD, 400
];

// count for duplicate field

Journal_Count_Map:
MAPPING LOAD
JournalNo,
Count(JournalNo) as JournalCount
RESIDENT Temp_Data
GROUP BY JournalNo;

// where clause about condition

NoConcatenate
Final_Data:
LOAD
CompanyCode,
JournalNo,
ledgerType,
entryType,
Amount
RESIDENT Temp_Data
WHERE NOT (ApplyMap('Journal_Count_Map', JournalNo, 0) >= 2 AND ledgerType = 4);

// drop temp table

DROP TABLE Temp_Data;

[ali_hijazi]

Hello Alex
well the if else statement works just fine
wondering what's the difference between the if else approach and the union in the set analysis
isn't the following read as "display the Journal No. where either entry type is GRP___ and ledger type = 4 or Ledger type is different from 4?

[alejandroquinones]

Hi @ali_hijazi ,

As far as I understand, the set modifier within the calculated dimension is doing its job, as it's pulling the only possible JournalNo. However, when you use sum(Amount), it sums values over the entire context. Therefore, for JournalNo. 12345, it uses 599 + 699 = 1298, which I understand is not what you require

I guess the main difference from an if() statement is that if() evaluates row-by-row so Amount = 699 is not available.

Regards,

Alex

[ali_hijazi]

the thing is that I have so many columns in the straight table; this means i need to put this condition on all columns 
I didn't use a measure because the report will be extremely slow (huge data)
adding them as dimensions is quiet faster