Creating a comma separated list in a field from multiple records

sicilianif — Fri, 09 Apr 2010 13:30:28 GMT

This is something that I already have a solution for, but am thinking that there might be an easier or better way.

What I have is a table with a vendor number and a type. A vendor can be of one or more types. The users want to see the types in a comma separated fashion. See before and after example below.

Here is how I am currently doing it:


 Vendors:
 LOAD * INLINE
 [ Vendor, VendorType
 123,A
 123,B
 124,A
 125,A
 125,C
 126,B ];
 ListTemp:
 LOAD
 'X' as VendorTypeList,
 1 as Rec,
 VendorType,
 Vendor
 RESIDENT Vendors;
 ListTemp2:
 LOAD
 Vendor,
 If (Previous(Vendor)=Vendor, Peek(VendorTypeList) & ' , ' & VendorType,VendorType) as VendorTypeList ,
 numsum( Rec, peek('RecCount')) as RecCount
 RESIDENT ListTemp
 ORDER BY Vendor,VendorType;
 DR0P TABLE ListTemp;
 VendorList:
 LOAD
 Vendor,
 MAXSTRING(VendorTypeList) as VendorTypeList
 RESIDENT ListTemp2
 GROUP BY Vendor
 ORDER BY RecCount;

 DR0P TABLE ListTemp2;

Any thoughts or ideas are greatly apreciated..

Frank

Creating a comma separated list in a field from multiple records

Oleg_Troyansky — Fri, 09 Apr 2010 13:34:40 GMT

try this:


load
 Vendor,
 concat(VendorType, ' ,') as VendorTypes
resident
 Vendors
group by
 Vendor
;

Creating a comma separated list in a field from multiple records

sicilianif — Fri, 09 Apr 2010 13:45:07 GMT

Thank you.. I knew I was making it much harder than it needed to be.

topic Creating a comma separated list in a field from multiple records in QlikView

Creating a comma separated list in a field from multiple records

Creating a comma separated list in a field from multiple records

Creating a comma separated list in a field from multiple records