Qlik Sense App Development

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Creator II

Concat with exclude current row value

Hello,

Please find the below scenario and provide a solution

Consider a straight table in a sheet where I have ShipmentId , productId and  new field with desired output called sub shipment

ShipmentId, ProductId, Subshipment

100 ,1000A, 101;102

101, 1000A, 100;102

102,1000A, 100;101

103,1000B,104

104,1000B,103

105,1000C, -

So what should be the expression for subshipment ?

Labels (3)

• Expression

1 Solution

Accepted Solutions
Highlighted
Creator II

I would suggest use most of the calculation or transform use in backend (script ) , and above example will handle your all requirement even if you have more than 11 Subshipment id's , since you want to you want to handle it on qliksense table (chart) here i have mentioned below expression.

=replace(Replace(Replace( Subshipment,';'&Aggr( nodistinct Concat(ShipmentId,';'),ProductId ),''),Aggr( nodistinct Concat(ShipmentId,';'),ProductId )&';',''),Aggr(nodistinct Concat(ShipmentId,';'),ProductId ),'')

thanku 😊

6 Replies
Highlighted
Creator II

Hi,

what is the logic for 3rd column(subshipment) ?

Highlighted
Creator II

Hi , Thanks for the response.

so Based on each of the productId shipment Id hast to be concatenated in each row and the shipment id in that particular row has to be omitted

Highlighted
Creator II

Hi ,

table:

ShipmentId, ProductId

100 ,1000A

101, 1000A

102,1000A

103,1000B

104,1000B

105,1000C
];

//table2:
left join(table)
ProductId,
concat(ShipmentId,';') as Subshipment
Resident table Group by ProductId ;

NoConcatenate
table2:
ShipmentId,
ProductId,

replace(Replace( Replace( Subshipment,';'&ShipmentId,'' ),ShipmentId&';',''),ShipmentId,'' )as Subshipment

Resident table;

Drop Table table;

Highlighted
Creator II

Hello ,

Thank you for the response. However I would like to have it in a expression in a table in qliksense. Also the fact is there will be multiple shipments for each product. Like one product can have 11 shipments too..

please suggest an expression for this.

Highlighted
Creator II