Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Qlik data load -- Multiple rows into a comma-delimited value

I have an Excel sheet with data like this:

CUSIPCounterParty
1234ABCCounterparty 1
1234ABCCounterparty 2
1234ABCCounterparty 3

Need output

CUSIPCounterparties
1234ABCCounterparty 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.

1 Solution

Accepted Solutions
Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik

View solution in original post

2 Replies
Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik