topic Set Analysis to limit row returns from Concat in QlikView

Set Analysis to limit row returns from Concat

Thu, 21 Oct 2010 02:48:40 GMT

trying to use Set Analysis to limit the number of rows returned from concat based on a field in the row, I've tried this many different ways and can't seem to get the correct results:

Note: OrderPcs is an Input Field that defaults to 0 but then could be changed by the user before the below code is ran.

ex.1

concat({<OrderPcs={0}>} distinct [Master Product Description]& '; ' &[Product Width]& '; ' &[Product Length]& '; ' &OrderPcs& '; ' &OrderWgt& '; ' &OrderNotes& ' | ', chr(13)&chr(10) , fieldindex('[Master Product Description]', [Master Product Description]))

an1 = returns everything even rows that are > 0 in the OrderPcs field.

ex.2

concat({<OrderPcs={5}>} distinct [Master Product Description]& '; ' &[Product Width]& '; ' &[Product Length]& '; ' &OrderPcs& '; ' &OrderWgt& '; ' &OrderNotes& ' | ', chr(13)&chr(10) , fieldindex('[Master Product Description]', [Master Product Description]))

an2 = returns absolutely nothing even though I have some records with 5 in the OrderPcs field.

I just want to limit the number of rows returned based on what value is in the OrderPcs field.

Side Note: the results are going into a variable that is then displayed in a text box for the user to review.

Added Note: if I only have one row that fits ex.2 code then that one row is captured and displayed, but more then one row and everything is blank.

Set Analysis to limit row returns from Concat

pover — Thu, 21 Oct 2010 04:58:37 GMT

William,

I was playing around with a similiar situation you describe and I had to do something like the following:

=concat( {$<Month={"=round(sum(BudgetInput))=1"}>} distinct Month,chr(10)&chr(13),fieldindex('Month',Month))

BudgetInput is an inputfield and even if I changed the some values to 1 in a chart, the chart was showing me and I was changing a sum(BudgetInput), in this case by Month, and there was really no value 1 in the field BudgetInput. I also had to put round because even if a put 1, QlikView changes the numbers that make up the sum and the number is never exactly 1, but rather 1.0000000372.

Regards.

Set Analysis to limit row returns from Concat

Thu, 21 Oct 2010 15:25:49 GMT

Thanks Karl,

I played around with what you gave me and came out with this:


concat({$<[Master Product Description]={"round(sum(OrderPcs))>0"}>} distinct [Master Product Description], chr(13)&char(10), fieldindex('<[Master Product Description]', <[Master Product Description]))

its not pretty but it works, so thx for the help!!!