Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
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
sunny_talwar

My bad, try this

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

View solution in original post

14 Replies
vinieme12
Champion III
Champion III

You should be trying somethinng like below in set analysis

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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
Creator
Creator
Author

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
Creator
Creator
Author

Hi Sunny,


Thanks again for trying to help.

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

flygstolen_fred
Creator
Creator
Author

I tried without success like this:

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

Any suggestions ?

sunny_talwar

Check the attached

Capture.PNG

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