Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

If nested table contains specific row

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:

OrderIdOrderNameOrderDate
123Mikael Persson2017-01-09

Invoice:

InvoiceIdInvoiceDateOrderId
78652017-01-10123

InvoiceLine:

InvoiceLineIdProductCodeProductPriceInvoiceId
56001JFH-FEE1007865
1 Solution

Accepted Solutions
Highlighted

Re: If nested table contains specific row

My bad, try this

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

View solution in original post

14 Replies
Highlighted
Champion II
Champion II

Re: If nested table contains specific row

You should be trying somethinng like below in set analysis

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

Highlighted

Re: If nested table contains specific row

try like this

if(productcode like 'JFH*', expression )

Highlighted

Re: If nested table contains specific row

May be like this

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

or using set analysis

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

Highlighted

Re: If nested table contains specific row

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

Highlighted
Creator
Creator

Re: If nested table contains specific row

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!

Highlighted

Re: If nested table contains specific row

May be like this

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

Highlighted
Creator
Creator

Re: If nested table contains specific row

Hi Sunny,


Thanks again for trying to help.

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

Highlighted
Creator
Creator

Re: If nested table contains specific row

I tried without success like this:

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

Any suggestions ?

Highlighted

Re: If nested table contains specific row

Check the attached

Capture.PNG

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