Remove null concatenation in pivot table

[azucna122]

Hi, I am new to qlik,
I have a pivot table with the dimensions group and subgroup.
the group field is a concatenation of group number and the group name (Num_Group & '- '& Group) and the Subgroup field has the same logic
( (Num_Subgroup & '- '& Subgroup)).
The problem is that I get a null concatenation in the filter panel.
in my dimension I was putting this expression: if(isnull(Group_concat or Group_concat='-'), Group_concat), but it still doesn't remove the null concatenation from the filter...
Can you help me, please.

[Nicolae_Alecu]

Hello,

In Edit Panel of Pivot Table , go to Data -> Dimensions and there just uncheck "Include null values" .

As for the filter pane :
Try this :  if(not match(Group_concat,'-'), Group_concat)

Best Regards,

[azucna122]

hello Nicolae_Alecu, thank you for your quick response.
I had that option disabled and it doesn't work for the filter, it removes it from the pivot table.
but the problem I have with the filter is that I get the null concatenation.

[BrunPierre]

Hi, I have a suspicion that you have actual blank values in "Group_concat", hence try this

=Aggr(Only({<Group_concat={"=Len(Trim(PurgeChar(Group_concat,'-')))>0"}>}Group_concat), Group_concat)

[azucna122]

hi BrunPierre

it doesn't work ... if that I leave you the reload script.
in the script I have a mapping load that reads from a file, I attach the script:

mapping_customer:
mapping load
Report,
Concept
from [mapping.xlsx] ;

customer:
cod_customer,
group,
subgroup,
applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',1)-1)) as customer_report,
applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',2)-1)) as customer_group,
applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',3)-1)) as customer_subgroup,

group & '-' & applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',2)-1)) as group_concat,
subgroup & '-' & applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',3)-1)) as subgroup_concat

from table_bbdd ;

the table reads the fields group_concat and subgroup_concat

[BrunPierre]

Try

mapping_customer:
mapping load Report
...

LOAD *,
If(Len(Trim(PurgeChar(Group_concat,'-'))), Group_concat) as T_Group_concat,
If(Len(Trim(PurgeChar(Group_concat,'-'))), subgroup_concat) as T_subgroup_concat
;
customer:
cod_customer,
...
from table_bbdd;

DROP Fields Group_concat,subgroup_concat;

RENAME Fields T_Group_concat to Group_concat, T_subgroup_concat to subgroup_concat;

[azucna122]

hi BrunPierre

I've done this:
If(Len(Trim(Group_concat)) > 0, Group_concat, Null()) & '-' & applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',2)-1)) as group_concat,
If(Len(Trim(subgroup_concat)) > 0, subgroup_concat, Null()) & '-' & applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',3)-1)) as subgroup_concat

and I still get the null in the filter and now in the pivot table, I need to do the concatenation like this and read from mapping

[BrunPierre]

@azucna122 Share a snippet of the pivot table and the expressions.

[azucna122]

no, it still doesn't work, I keep getting null ,
is there any other way to put that the applymap is not null when doing the concatenacion.
because the If(Len(Trim(Group_concat)) > 0, Group_concat, Null()) does it well, but applymap('mapping_customer',left(cod_customer,index(cod_customer,'.'.',3)-1),Null()) is still null.
and when I do the containment I get (null - null)

[azucna122 · Accepted solution]

hi BrunPierre,

already works with this sintexis:

IF(len(trim(group)) > 0, group & '-' & applymap('mapping_customer',left(cod_customer,index(cod_customer,'.',2)-1)), null()) as group_concat

thank you for your concern and for answering my questions so quickly!