topic Re: Concat with exclude current row value in App Development

Concat with exclude current row value

pradeep92 — Sat, 16 Nov 2024 05:10:49 GMT

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

Re: Concat with exclude current row value

mahaveerbiraj — Wed, 24 Jul 2019 14:21:35 GMT

Hi,

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

Re: Concat with exclude current row value

pradeep92 — Wed, 24 Jul 2019 14:29:10 GMT

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

Re: Concat with exclude current row value

mahaveerbiraj — Wed, 24 Jul 2019 15:10:15 GMT

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;

Re: Concat with exclude current row value

pradeep92 — Wed, 24 Jul 2019 15:15:25 GMT

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.

Re: Concat with exclude current row value

mahaveerbiraj — Thu, 25 Jul 2019 06:42:24 GMT

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 😊

Re: Concat with exclude current row value

pradeep92 — Thu, 25 Jul 2019 11:09:47 GMT

Thank you for your valuable suggestion. Means a lot!