Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 😊
Hi,
what is the logic for 3rd column(subshipment) ?
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
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;
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.
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 😊
Thank you for your valuable suggestion. Means a lot!