topic Re: Concat() with SetAnlaysis and include Blankvalues in QlikView

Concat() with SetAnlaysis and include Blankvalues

Lakshmanan — Mon, 12 Aug 2024 16:02:34 GMT

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

Re: Concat() with SetAnlaysis and include Blankvalues

Digvijay_Singh — Mon, 19 Aug 2024 08:11:30 GMT

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)

Re: Concat() with SetAnlaysis and include Blankvalues

Lakshmanan — Thu, 22 Aug 2024 09:13:29 GMT

Thanks ,it worked.

Re: Concat() with SetAnlaysis and include Blankvalues

seanbruton — Thu, 17 Oct 2024 15:03:06 GMT

Superb, very helpful solution!!!