Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
surajdhall
New Contributor III

supress rows in UI

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.

K
M

 

 

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-

Capture.JPG

 

Please help.

Labels (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: supress rows in UI

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

Re: supress rows in UI

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.
surajdhall
New Contributor III

Re: supress rows in UI

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.

Partner
Partner

Re: supress rows in UI

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

Data with concatenate.png

 

 

 

 

 

 

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

Data with outer join.png

 

 

 

 

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

Screenshot_1.png

 

 

Regards.

Dan.

surajdhall
New Contributor III

Re: supress rows in UI

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.

 

 

 

Partner
Partner

Re: supress rows in UI

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

Re: supress rows in UI

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

 

Capture.PNG

 

 

Father/Husband/BI Developer
Partner
Partner

Re: supress rows in UI

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

Re: supress rows in UI

Thank you so much