Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Techies,
Here I have two tables:
Table1:
Snapshot_Month | Products_Aggregate |
Dec | A,B,C,D,E |
Nov | B,C,D |
Oct | A,F |
Table2:
Snapshot_Month | Products | Ratingsum |
Dec | A | 100 |
Dec | B | 200 |
Dec | C | 300 |
Dec | D | 400 |
Dec | E | 100 |
Nov | A | 100 |
Nov | B | 200 |
Nov | C | 300 |
Nov | D | 400 |
Nov | E | 100 |
Oct | A | 100 |
Oct | B | 200 |
Oct | C | 300 |
Oct | D | 400 |
Oct | E | 100 |
In a straight table, Snapshot_month is the dimension, Count(Products) is the expression.
Here, I need to pass Products_Aggregate via set expression to filter the products.
For example:
This is how we need the set expression.
Snapshot_Month | Expression |
Dec | Count({$<Product={'A,B,C,D,E'}>}Product) |
Nov | Count({$<Product={'B,C,D'}>}Product) |
Oct | Count({$<Product={'A,F'}>}Product) |
Required Output:
Snapshot_Month | Output |
Dec | 5 |
Nov | 3 |
Oct | 2 |
Please let me know how to achieve this.
Hi Pradosh,
I can not count the Product_Aggregate. Because Products count may differ in real scenario. I need to pass the Product_Aggregate set to count the Product.
Thank you.
Hi Kaanerisen,
As mentioned above, I can not count the Product_Aggregate. Because Products count may differ in real scenario. I need to pass the Product_Aggregate set to count the Product.
Thank you.
OK then how about this 🙂
COUNT(IF(WildMatch(prodAgg,product&',*','*,'&product,'*,'&product&',*',product),product))
Your required output is mentioned in your post is
Snapshot_Month | Output |
Dec | 5 |
Nov | 3 |
Oct | 2 |
But Oct should be 1 because there is only Product A data that match in table 1 in October.
If you want to use set analysis, You should create a uniqueID for month&product.
load AutoNumber(month&'|'&product) as key,* Inline [
month,product,value
Dec,A,100
Dec,B,200
Dec,C,300
Dec,D,400
Dec,E,100
Nov,A,100
Nov,B,200
Nov,C,300
Nov,D,400
Nov,E,100
Oct,A,100
Oct,B,200
Oct,C,300
Oct,D,400
Oct,E,100
]
Count({<key={"=WildMatch(prodAgg,product&',*','*,'&product,'*,'&product&',*',product)"}>}key)
Hope it helps.