topic Re: If nested table contains specific row in QlikView

If nested table contains specific row

flygstolen_fred — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: If nested table contains specific row

vinieme12 — Tue, 06 Jun 2017 14:21:34 GMT

You should be trying somethinng like below in set analysis

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

Re: If nested table contains specific row

avinashelite — Tue, 06 Jun 2017 14:21:57 GMT

try like this

if(productcode like 'JFH*', expression )

Re: If nested table contains specific row

sunny_talwar — Tue, 06 Jun 2017 14:22:54 GMT

May be like this

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

or using set analysis

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

Re: If nested table contains specific row

sunny_talwar — Tue, 06 Jun 2017 14:24:41 GMT

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

Re: If nested table contains specific row

flygstolen_fred — Tue, 06 Jun 2017 14:34:09 GMT

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!

Re: If nested table contains specific row

sunny_talwar — Tue, 06 Jun 2017 14:41:02 GMT

May be like this

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

Re: If nested table contains specific row

flygstolen_fred — Tue, 06 Jun 2017 14:45:58 GMT

Hi Sunny,

Thanks again for trying to help.

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

Re: If nested table contains specific row

flygstolen_fred — Tue, 06 Jun 2017 15:02:10 GMT

I tried without success like this:

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

Any suggestions ?

Re: If nested table contains specific row

sunny_talwar — Tue, 06 Jun 2017 15:02:45 GMT

Check the attached

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

Re: If nested table contains specific row

flygstolen_fred — Tue, 06 Jun 2017 15:10:36 GMT

I'm still stucked with only the "false"-alternative.

Re: If nested table contains specific row

sunny_talwar — Tue, 06 Jun 2017 15:11:17 GMT

Would you be able to share a sample where you are trying to do this?

Re: If nested table contains specific row

flygstolen_fred — Tue, 06 Jun 2017 15:31:12 GMT

Hi Sunny,

I seems to work if the "order" only contains products that fulfills the condition, but not when the order also have other products.

Thanks again!

Re: If nested table contains specific row

sunny_talwar — Tue, 06 Jun 2017 15:39:44 GMT

My bad, try this

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

Re: If nested table contains specific row

flygstolen_fred — Tue, 06 Jun 2017 16:08:12 GMT

Thanks Sunny for helping out and your patience.