Connectivity & Data Prep

[Anonymous]

I have an Excel sheet with data like this:

CUSIP	CounterParty
1234ABC	Counterparty 1
1234ABC	Counterparty 2
1234ABC	Counterparty 3

Need output

CUSIP	Counterparties
1234ABC	Counterparty 1, Counterparty 2, Counterparty 3

In the data load scripts (or other other methods?), how can I collapse the rows into a comma delimited field?

I need to do this in the load scripts because I have other tables that depend on the uniqueness of CUSIP and I need to collapse the rows.

[Michael_Tarallo · Accepted solution]

Hi Khoa,

Try this:

[rawData]:
LOAD CUSIP,
    CounterParty
FROM

(ooxml, embedded labels, table is Sheet1);


Second:
NoConcatenate LOAD *
Resident rawData
Order By CUSIP;
DROP Table rawData;

New:
NoConcatenate LOAD
    CUSIP,
    Concat(CounterParty, ',') as CounterParties

Resident Second
Group By CUSIP;
DROP Table Second;

Note the output:

I attached a QlikView sample here and also a Qlik Sense sample in the other post.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[Michael_Tarallo]

Here is a Qlik Sense sample - attached:

If using Qlik Sense Desktop. please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5

NOTE the FROM clause uses the lib:// syntax connecting to a Folder Connection named "Desktop"

[rawData]:
LOAD
    CUSIP,
    CounterParty
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);



Second:  
NoConcatenate LOAD *  
Resident rawData  
Order By CUSIP;  
DROP Table rawData;  

New:  
NoConcatenate LOAD  
CUSIP,  
Concat(CounterParty, ',') as CounterParties  
Resident Second  
Group By CUSIP;  
DROP Table Second;  

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik