Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following tables with customers and products. I would like to concatenate all the products that a customer bought last (i.e. with Max Date).
CUST:
Load * Inline [
cid, name
1, a
2, b
];
PRODS:
Load * Inline [
date, cid, prod
1, 1, 'pa'
2, 1, 'pa'
2, 1, 'pb'
1, 2, 'pa'
2, 2, 'pb'
3, 2, 'pc'
];
I came very close, but it's taking the total max date of all the dates, not per customer. So for date=3, it is giving null for customer A.
=Aggr( {<date={"$(=Max(date))"}>} Concat( distinct type, ','), cid)
$() is resolved once per chart, so it won't work in your case where you have a different Max(date) per cid. I think your solution is:
Aggr(Concat(distinct if(date = Aggr(nodistinct max(date),cid), prod), ','), cid)
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Works like a charm, but it looks terribly complicated fur such a simple problem. Doing this in some other programming language would be much simpler.
$() is resolved once per chart, so it won't work in your case where you have a different Max(date) per cid. I think your solution is:
Aggr(Concat(distinct if(date = Aggr(nodistinct max(date),cid), prod), ','), cid)
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Works like a charm, but it looks terribly complicated fur such a simple problem. Doing this in some other programming language would be much simpler.