Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
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 ? 

Kindly advise 

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Hi Pradeep,

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 😊

View solution in original post

6 Replies
Highlighted
Creator II
Creator II

Hi,

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

Highlighted
Creator II
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
Creator II

Hi , 

Please find below logic for your requirement.

table:
load * Inline [

ShipmentId, ProductId

100 ,1000A

101, 1000A

102,1000A

103,1000B

104,1000B

105,1000C
];

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

NoConcatenate
table2:
load
ShipmentId,
ProductId,

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

Resident table;

Drop Table table;

Highlighted
Creator II
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
Creator II

Hi Pradeep,

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 😊

View solution in original post

Highlighted
Creator II
Creator II

Thank you for your valuable suggestion. Means a lot!