Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
surajdhall
Contributor III
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
daanciorea
Partner - Contributor III
Partner - Contributor III

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

View solution in original post

8 Replies
daanciorea
Partner - Contributor III
Partner - Contributor III

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
Contributor III
Contributor III
Author

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.

daanciorea
Partner - Contributor III
Partner - Contributor III

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
Contributor III
Contributor III
Author

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.

 

 

 

daanciorea
Partner - Contributor III
Partner - Contributor III

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

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

 

Capture.PNG

 

 

Father/Husband/BI Developer
raman_rastogi
Partner - Creator III
Partner - Creator III

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

Thank you so much