Concat() with SetAnlaysis and include Blankvalues

[Lakshmanan]

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

[Digvijay_Singh · Accepted solution]

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)

[Lakshmanan]

Thanks ,it worked.

[seanbruton]

Superb, very helpful solution!!!