Solved: concatenate text field of a alphanumeric field bas... - Qlik Community

sadiaasghar · ‎2020-09-03

I want to concatenate text value of a alphanumeric field i.e. Progress Description /Achievement based on month and project in a single row. I want to display it on current Month, Current year basis but the problem is some project has sub projects so they have multiple values for a single month i want them to concatenate by a line break. Or if the value is numeric i want to show cumulative progress description for that specific month.

As you can see in image i want them in a single line item against each project.

Kushal_Chawda · ‎2020-09-04

@sadiaasghar You may need to create the additional dimension and add it in your straight table as you want to perform different aggregation based on condition. Hence, you need additional dimension in your table to work correctly. You can hide that dimension if you want but I would advise to keep it as it makes sense

data:
LOAD [Fiscal Year], 
     [Measure/Department], 
     Project, 
     [Sub-Project], 
     Month, 
     if(IsText([Progress Description]),'Text','Number') as Progress_Description_Category,
     [Monthly Step weightage], 
     [Overall Step Weightage], 
     [Progress Target], 
     [Progress Description]
FROM
[C:\Data for concatenate.xlsx]
(ooxml, embedded labels, table is Sheet1);

Now you can create straight table

Dimensions:

[Fiscal Year],
[Measure/Department],
Project

Progress_Description_Category // you can hide it from presentation if don't want

Expression:

=if(Progress_Description_Category='Text', Concat(distinct [Progress Description],chr(10)),sum( [Progress Description]))

View solution in original post

sadiaasghar · ‎2020-09-03

@Kushal_Chawda Please help me with this. I have tried this but it's not returning anything.

if(istext([Progress Description]),Aggr(Concat(DISTINCT {<Month = {$(=Max(Month))}>}[Progress Description], ','),Project))

Kushal_Chawda · ‎2020-09-03

@sadiaasghar for numeric do you want to do Sum of values..like here 65.7+5000?

sadiaasghar · ‎2020-09-04

Yes for numeric, i want to show cumulative sum of progress description, and for text concat on the basis of project & maxmonth, maxyear.

sadiaasghar · ‎2020-09-04

I am concatenating text value and cumulative of numeric value separately and it's working fine. But how can i show that in a single expression. And Max Month and year modifier are not working in set analysis.

Concat({<Month={'>=$(=MonthStart(Max(Month)))<=$(=MonthEnd(Max(Month)))'}>}[StrProgress Description],chr(10),Project)

Kushal_Chawda · ‎2020-09-04

@sadiaasghar Try below

Dimensions:

FiscalYear

Deprtment

Project

Expression:

= aggr(if(istext([StrProgress Description]), concat([StrProgress Description],chr(10)), sum([StrProgress Description])),FiscalYear,Department,Project)

sadiaasghar · ‎2020-09-04

single condition for concat is working but when i want to combine result of text and numeric value it's returing only numeric values.

IF(IsText([Progress Description]),Concat({<year_month_num = {$(=Max(year_month_num))}>}[StrProgress Description],chr(10),Project),IF(IsNum([Progress Description]),Concat({<year_month_num = {$(=Max(year_month_num))}>}Cumulative)))

sadiaasghar · ‎2020-09-04

I have written logic for cumulative sum of progress description in script.

Kushal_Chawda · ‎2020-09-04

@sadiaasghar did you try my expression? or would you be able to share sample data with expected output?

sadiaasghar · ‎2020-09-04

@Kushal_Chawda i have tried your expression and it's not working for me it's not concatenating anything and also placing zero in all numeric values. I have already shared sample data above. What i want is concatenate values of Progress Description, concatenate as it is if value is text but if value is numeric i want to replace progress description by it's cumulative sum.

concatenate text field of a alphanumeric field based on another field