To Display common records In a third table C using intersection of row results already filtered in two other displayed tables A and B

Ashley33 — Thu, 17 Jun 2021 07:21:45 GMT

Hello Dears,

Please note that i have loaded the attached excel data StockData.xlsx in load script:

TableStock:
LOAD
Country,
"Year",
"Month",
"Item Name",
IC,
"Stock Qty",
DataType
FROM [lib://DATA/bkup/StockData.xlsx]
(ooxml, embedded labels, table is TableStock);

TableStockComparison:
LOAD
CountryComparison,
YearComparison,
MonthComparison,
"Item Name Comparison",
"IC Comparison",
"Stock Qty Comparison",
"DataTypeComparison"
FROM [lib://DATA/bkup/StockData.xlsx]
(ooxml, embedded labels, table is TableStockComparison);

Then i created below qliksense page:

Can you please help me solve the following 3 user requirements:

1. TableA user requirements:

Using TableStock column filters on the left pane (Country, Year, Month), how to Write Expression to display item names having avg(IC) <= 100 from script loaded TableStock ?

Example: Using TableStock filters on left pane in screenshot , for Australia only the two records with avg(IC) <= 100 is displayed for the two items named: Apple and Orange

Note: The records having avg(IC) = null should be excluded from displayed Table A

Country	Year	Month	Item Name	=avg(IC)	sum([Stock Qty])
Australia	2020	3	Apple	75.00	16719
Australia	2020	3	Orange	90.00	14118

2. TableB user requirements:

Using TableStockComparison column filters on the right pane (CountryComparison, YearComparison, MonthComparison), how to Write Expression to display item names comparisons having avg(IC Comparison) > 300 from script loaded TableStockComparison

Example: Using TableStockComparison filters on right pane in screenshot , for England only the one record with avg(IC Comparison) > 300 is displayed for the one item named: Orange

Note: The records having avg(IC Comparison) = null should be excluded from displayed Table B

CountryComparison	YearComparison	MonthComparison	Item Name Comparison	=avg([IC Comparison])	sum([Stock Qty Comparison])
England	2020	3	Orange	400.00	88790

3. TableC user requirements:

Using TableStockComparison column filters on the right pane (CountryComparison, YearComparison, MonthComparison)

And

Using TableStockComparison column filters on the right pane (CountryComparison, YearComparison, MonthComparison),

how to Write an Expression to display COMMON item names (that is the intersection of common item names) from the results of the above displayed Table A and Table B

Example: Using the example filter selections from above, the below common item Orange should be displayed in the 3rd table C as it exists and is common in both tables A for Australia data and Table B for England data

Note: The records having avg(IC) = null and avg(IC Comparison) = null should be excluded from displayed Table C

Country	Year	Month	Item Name	avg(IC) <= 100	sum([Stock Qty])	CountryComparison	YearComparison	MonthComparison	Item Name Comparison	avg([IC Comparison]) > 300	sum([Stock Qty Comparison])
Australia	2020	3	Orange	90.00	14118	England	2020	3	Orange	400.00	88790

topic To Display common records In a third table C using intersection of row results already filtered in two other displayed tables A and B in App Development

To Display common records In a third table C using intersection of row results already filtered in two other displayed tables A and B