Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pradeep92
Partner - Creator II
Partner - 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
mahaveerbiraj
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
mahaveerbiraj
Creator II
Creator II

Hi,

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

pradeep92
Partner - Creator II
Partner - Creator II
Author

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 

mahaveerbiraj
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;

pradeep92
Partner - Creator II
Partner - Creator II
Author

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.

mahaveerbiraj
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 😊

pradeep92
Partner - Creator II
Partner - Creator II
Author

Thank you for your valuable suggestion. Means a lot!