Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
Someone help me with below concat set analaysis chart expression.
Load Script
SET DateFormat='DD/MM/YYYY';
[Table1]:
Load * Inline[
PurchaseNo,SalesNo,LinkNo,ReceiptNo,Amount, EndDate, Startdate
4156,TS12,PRI108,123,200,'21/08/2024','01/08/2024'
4156,TS13,PRI101,143,300,'21/08/2024','01/08/2024'
4156,TS14,PRI104,53,300,'21/08/2024','01/08/2024'
4823,TS234,PRI100,454,400,'21/08/2024','01/08/2024'
4156,TS15,PRI106,443,300,'02/08/2024','01/08/2024'
];
Left Join ([Table1])
Load PurchaseNo,
Concat(LinkNo,',') as LinkConcatNo
Resident [Table1]
group by PurchaseNo;
I have created a straight table with PurchaseNo , LinkConcatNo,ActiveReceipts,ActiveReceiptAmount
ActiveReceipts =Aggr(concat({<[Enddate] = {">=$(=date(Today()))"} , [StartDate] = {"<=$(=date(Today()))"} >} ReceiptNo,',',LinkNo) ,PurchaseNo )
but the above expression returns null.
Output expected :
ActiveReceipts order should match with LinkConcatNo and if no ActiveReceipts for that purchase should return as '-' (concat shouldnot skip the null ones).
LinkNo order and ActiveReceipt order should be same order.
PurchaseNo | LinkConcatNo | Active Receipts |
4156 | PRI101,PRI104,PRI106,PRI108 | 143,53,-,123 |
Something like this may be -
SET DateFormat='DD/MM/YYYY';
[Table1]:
Load * Inline [
PurchaseNo,SalesNo,LinkNo,ReceiptNo,Amount, EndDate, Startdate
4156,TS12,PRI108,123,200,'21/08/2024','01/08/2024'
4156,TS13,PRI101,143,300,'21/08/2024','01/08/2024'
4156,TS14,PRI104,53,300,'21/08/2024','01/08/2024'
4823,TS234,PRI100,454,400,'21/08/2024','01/08/2024'
4156,TS15,PRI106,443,300,'02/08/2024','01/08/2024'
];
Left Join ([Table1])
Load PurchaseNo,
Concat(LinkNo,',',ReceiptNo) as LinkConcatNo
Resident [Table1]
group by PurchaseNo;
Expression for ActiveReceipt -
concat(aggr(if(Startdate <= today() and EndDate >= today(),ReceiptNo,'-'),LinkNo,ReceiptNo),',',ReceiptNo)
Something like this may be -
SET DateFormat='DD/MM/YYYY';
[Table1]:
Load * Inline [
PurchaseNo,SalesNo,LinkNo,ReceiptNo,Amount, EndDate, Startdate
4156,TS12,PRI108,123,200,'21/08/2024','01/08/2024'
4156,TS13,PRI101,143,300,'21/08/2024','01/08/2024'
4156,TS14,PRI104,53,300,'21/08/2024','01/08/2024'
4823,TS234,PRI100,454,400,'21/08/2024','01/08/2024'
4156,TS15,PRI106,443,300,'02/08/2024','01/08/2024'
];
Left Join ([Table1])
Load PurchaseNo,
Concat(LinkNo,',',ReceiptNo) as LinkConcatNo
Resident [Table1]
group by PurchaseNo;
Expression for ActiveReceipt -
concat(aggr(if(Startdate <= today() and EndDate >= today(),ReceiptNo,'-'),LinkNo,ReceiptNo),',',ReceiptNo)
Thanks ,it worked.
Superb, very helpful solution!!!