topic Re: supress rows in UI in QlikView

supress rows in UI

surajdhall — Sat, 16 Nov 2024 03:31:21 GMT

Hi All,

I have 2 tables brought into one table by concatenation.
My requirement is to only see products that are present in first table(sales table) in my front end straight table. So that means i don't want to see below products in straight table.

Script-->

Sales:
LOAD * INLINE [
Product, Sales
A, 120
A, 10
B, 140
B, 15
C, 100
];

Concatenate(Sales)
//Budget:
LOAD * INLINE [
Product, Budget
A, 150
K, 180
M, 50
];

output-

Please help.

Re: supress rows in UI

daanciorea — Tue, 21 May 2019 06:23:13 GMT

Hi,

Try to use left join statement instead of concatenate statement with the purpose of don't have the budget data that don't have sales.

I hope this solve your issue.

Dan.

Re: supress rows in UI

surajdhall — Tue, 21 May 2019 06:25:36 GMT

Thanks for your reply.

My original tables have multiple columns and doing left join will effect the rows in sales table. So i want control this only in front end.

Re: supress rows in UI

daanciorea — Tue, 21 May 2019 06:50:43 GMT

Your problem is the concatenate statment because if you ase ussing this your data isn't related. Attached the resultant table (Data with concatenate).

If you use the outer join function instead your data looks like (Data with outer join)

and then you can use this expresion to sum only the budget that are sales > 0:

sum({<Budget={"=sum(Sales)>0"}>} Budget)

The final table looks like

Regards.

Dan.

Re: supress rows in UI

surajdhall — Tue, 21 May 2019 07:21:51 GMT

Thanks for detailed explanation and your time. I understand you are suggesting me to do outer join. i am trying to avoid joins at the moment as there is lot going based on the final table in the live application.

I prefer doing it in front end. Is it possible to use Aggr() ? So our requirement is to show only those budget numbers that has sales.

Re: supress rows in UI

daanciorea — Tue, 21 May 2019 07:41:59 GMT

Then use this: If(sum(Sales) > 0,sum(Budget))

Re: supress rows in UI

dapostolopoylos — Tue, 21 May 2019 09:17:36 GMT

You can try an approach with some set analysis, check the attached for further details

Re: supress rows in UI

raman_rastogi — Tue, 21 May 2019 09:51:41 GMT

Try With Calculated Dimension
Calculated Dimension - =if(aggr(sum(Sales),Product)>0,Product)
And Suppress values when value is NUll.
Thanks
Raman

Re: supress rows in UI

surajdhall — Tue, 21 May 2019 22:03:06 GMT

Thank you so much