Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Lakshmanan
Partner - Contributor III
Partner - Contributor III

Concat() with SetAnlaysis and include Blankvalues

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
1 Solution

Accepted Solutions
Digvijay_Singh

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)

 

Digvijay_Singh_0-1724055071100.png

 

View solution in original post

3 Replies
Digvijay_Singh

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)

 

Digvijay_Singh_0-1724055071100.png

 

Lakshmanan
Partner - Contributor III
Partner - Contributor III
Author

Thanks ,it worked.

seanbruton
Luminary Alumni
Luminary Alumni

Superb, very helpful solution!!!