Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a table in datamodel like below
Rec No | product | Flag |
1 | AB | 0 |
1 | AA | 0 |
2 | BB | 0 |
2 | BC | 0 |
3 | CV | 0 |
3 | CD | 0 |
3 | CF | 0 |
4 | GH | 0 |
4 | GT | 0 |
4 | GY | 0 |
1 | AC | 1 |
2 | BD | 1 |
3 | CN | 1 |
i want output like below , because rec_no 4 should not share with both flag values , it has value 0 only
Remaining all records share flag values 1 and 0 both
Rec_ No | Product |
4 | GH;GT;GY |
How to write chart expession in front end to get above output
maybe this expression works for you?
Concat({$<[Rec No]={"=Max(Flag=0)"}>} product,';')
Load RecNo
Where CountFlag < 2;
Load RecNo, Count(distinct Flag) as CountFlag
From YourTable
Group by RecNo;
Left Join
Load RecNo, Concat(Product,';') as Product
From YourTable
Group By RecNo;
Should work here, I believe.
Hello,
If you are looking for an expression to use in chart, then you can use: Aggr(NODISTINCT Count(DISTINCT Flag), Rec)
This expression will count all the unique flags per each individual record number. Therefore, it will return the number 1 for all the records where only one of the two flags is found or 2 where both flags are found. Here is an example:
1. Load the dataset:
2. Create a new table and use measure expression: If(Aggr(NODISTINCT Count(DISTINCT Flag), Rec)= 1, Rec, Null())
3. Then add No product and Flag as dimensions
4. Go to Add-ons > Data handling and un-check the value "Include zero values". The output is:
I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.
Thank you for your feedback,
But I want output like below (Noproduct are in single row)
Rec Noproduct
4 GH;GT;GY
is this possible ?
Here is one way to do it:
In the Rec No column in the Properties, Data add a limitation of Fixed number, Top 1, Deselect Show others
@Lisa_P ,
if table like below
Rec No | product | Flag |
1 | AB | 0 |
1 | AA | 0 |
2 | BB | 0 |
2 | BC | 0 |
3 | CV | 0 |
3 | CD | 0 |
3 | CF | 0 |
4 | GH | 0 |
4 | GT | 0 |
4 | GY | 0 |
1 | AC | 1 |
2 | BD | 1 |
3 | CN | 1 |
5 | FG | 1 |
6 | QA | 1 |
7 | ZA | 0 |
8 | WA | 0 |
output
Rec_No | product |
4 | GH;GT;Gy |
7 | ZA |
8 | WA |
is this possible ?
I just copied your new table in and this is the result:
I also Ignored zeros in Add-ons
Hi
But I don't want
5 FG
6 QA
Because for those Flag=1,
I want ouput like below
Rec_No | product |
4 | GH;GT;Gy |
7 | ZA |
8 | WA |
Beacuse rec_no 4,7,8 has shared flag=0 values only , for these there is no flag=1 values
I want only flag =0 rec_no records
maybe this expression works for you?
Concat({$<[Rec No]={"=Max(Flag=0)"}>} product,';')
Thank you so much ,Its worked