Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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,
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.
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)
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
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;
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
@azucna122 Share a snippet of the pivot table and the expressions.
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)
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!