Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
pradeep92
Contributor 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
Contributor

Re: Concat with exclude current row value

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 😊

6 Replies
mahaveerbiraj
Contributor

Re: Concat with exclude current row value

Hi,

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

pradeep92
Contributor II

Re: Concat with exclude current row value

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
Contributor

Re: Concat with exclude current row value

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
Contributor II

Re: Concat with exclude current row value

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
Contributor

Re: Concat with exclude current row value

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
Contributor II

Re: Concat with exclude current row value

Thank you for your valuable suggestion. Means a lot!