E () not working as expected

[jduluc12]

Hi

My data looks like this.

Item:

ID

1

2

3

5

Txn:

ID	Type	Amt
1	bill	50
2	bill	60
3	SO	46

My goal is to print the ID which does not have SO type in the Transaction table.

My expression looks like this.

Concat({<[Item.ID]=e({$<[Txn.Type]={'SO'}>}[Item.ID])>}[Item.ID], ',')

it shows 1, 2

but the expected is

1,2,5

What is wrong here?

Thanks

[tresesco]

Table reference is the issue here. You could instead try like:

=Concat( {<Item.ID-=p({$<Txn.Type={'SO'}>}Txn.ID)>} Item.ID, ',')

[jduluc12]

Does not seem to solve the issue.

[MarcoWedel]

maybe another method could be:

Concat({$-$<Type={'SO'}>} ID, ',')

[jduluc12]

unfortunately this also did not work

[jduluc12]

I think  i misses an important detail here.

there is another column customer in Txn table.

ID	Type	Amt	Customer
1	bill	50	A
2	bill	60	A
3	SO	46	B

And there is a customer filter. I select customer A, so the current data excludes Item 5, so it does not work i.e. it does not bring the 5 in the concatenated list. 

not sure if it will help me getting a solution for it.

[tresesco · Accepted solution]

Yes, that selection has impact. You can try :

=Concat( {<Item.ID-=p({1<Txn.Type={'SO'}>}Txn.ID)>} Item.ID, ',')

[jduluc12]

It did not solve the issue exactly but gave me an idea of how to solve it differently.

I created an island table with Item.id used that instead of the Item table. since that island table has no connection with the customer so it does not get filtered by the selection of the customer.

Concat( {<ItemList.ID-=p({$<Txn.Type={'SO'}>}Txn.ID)>} ItemList.ID, ',')

thanks for all help

[jduluc12]

It did not solve the issue exactly but gave me an idea of how to solve it differently.

I created an island table with Item.id used that instead of the Item table. since that island table has no connection with the customer so it does not get filtered by the selection of the customer.

Concat( {<ItemList.ID-=p({$<Txn.Type={'SO'}>}Txn.ID)>} ItemList.ID, ',')

thanks for all help