If nested table contains specific row

[flygstolen_fred]

Hi,

I'm trying to get a list of all orders done that contains a specific productcode that starts with "JFH".

I tried with "Only"-function but I doesn't seem to work. Any help very appreciated, thanks!

Order:

OrderId	OrderName	OrderDate
123	Mikael Persson	2017-01-09

Invoice:

InvoiceId	InvoiceDate	OrderId
7865	2017-01-10	123

InvoiceLine:

InvoiceLineId	ProductCode	ProductPrice	InvoiceId
56001	JFH-FEE	100	7865

[vinieme12]

You should be trying somethinng like below in set analysis

=Count({< ProductCode = {'JHF-*'} >}  OrderID)

[avinashelite]

try like this

if(productcode like 'JFH*', expression )

[sunny_talwar]

May be like this

If(WildMatch(ProductCode, 'JFH*'), OrderId)

or using set analysis

{<OrderId = {"=WildMatch(ProductCode, 'JFH*')"}>}

[sunny_talwar]

This will only find the productcode which starts with JHF, but to find orderid (which can have multiple productcode), we might need to use a search string on OrderId

[flygstolen_fred]

Hi,

Thanks all for trying to help.

Maybe my question wasn't specified enough.

There can be multiple product lines matching a single order and I wanna use a "filter pane", with a true / false statement like: Order contains "JFH-products", true / false.

Thanks!

[sunny_talwar]

May be like this

Aggr(If(WildMatch(Concat(DISTINCT '|' & ProductCode & '|', ','), '|JFH*|'), 'True', 'False'), OrderId)

[flygstolen_fred]

Hi Sunny,

Thanks again for trying to help.

That didn't work out for me, only showing the false-alternative.

[flygstolen_fred]

I tried without success like this:

aggr(Only(if(LEFT(ProductCode, 3) = 'JFH', 'True', 'False')), OrderId)

Any suggestions ?

[sunny_talwar]

Check the attached

=Aggr(If(WildMatch(Concat({1}DISTINCT '|' & ProductCode & '|', ','), '|JFH*|'), 'True', 'False'), OrderId)