Comma separated values

[Nikhil2725]

Hi All,

I have table below:

user_id	Subject_access_tracking_id	subject_id	Lender_offer_id
2	27254	4	1154
2	158956	1	1154
2	158957	2	1154
2	158958	3	1154
7	7080	4	3
7	20026	1	3
12	149	4	4
12	158	1	4
12	166	2	4
12	167	3	4
13	40	1	5
13	41	2	5
13	42	3	5
13	44	4	5

However, I want to display like below:

user_id	Lender_offer_id	Selection_order
2	1154	4,1,2,3
7	3	4,1
12	4	4,1,2,3
13	5	1,2,3,4

Kindly help me in displaying.

[Shubham_Deshmukh]

Hi @Nikhil2725 ,

You can do this using resident load on original script,

load
Concat(subject_id,',') as newSubID
resident Data
Group by user_id;

[Nikhil2725]

Hi Shubham_Deshmukh,
I tried, But its not working...
subject:
LOAD `subject_access_track_id`,
`subject_id`,
`user_id`,
`lender_offer_id`;
SQL SELECT `subject_access_track_id`,
`subject_id`,
`user_id`,
`lender_offer_id`
FROM SUBJECTACCESSTRACKING;
load
Concat(subject_id,',') as newSubID
resident subject
Group by user_id;

[Shubham_Deshmukh]

We have simpler way to do this,

Dimension : user_id

Exp1 : Lender_offer_id

Exp2 :  =Concat( distinct subject_id,',',subject_id)

[Nikhil2725]

Hi Shubham_Deshmukh,
Im getting the above output which you have showed, But I want output which I specified In the above table....
Like for the user_id=2, I want the selection order has 4,1,2,3 as per the subject_access_tracking_id.
For me also Im getting like for the user_id=2, Im getting 1,2,3,4
For the user_id=7, I want to display has 4,1
For the user_id=12, I want to display has 4,1,2,3
For the user_id=13, I want to display has 1,2,3,4

[Shubham_Deshmukh]

Hi,

If possible add RowNo() in your script along with other fields and use below exp , working for me : 

=Concat(distinct subject_id,',', [RowNo()])

[Shubham_Deshmukh]

If you are loading this data from database then RowNo() will not work so use Resident load in that case to merge it with other fields.

[Nikhil2725]

Can u share the code snippets??

[Shubham_Deshmukh]

I cannot share the app, just let me know you have DB script or loading data from excel?
As I am loading from EXCEL, it will be like below,

LOAD user_id, 
     Subject_access_tracking_id, 
     subject_id, 
     Lender_offer_id,
     RowNo()
FROM
[C:\Users\KT3028\Desktop\test.xlsx]
(ooxml, embedded labels, table is Sheet2);

But if your are loading data from DB, then it won't work. You need to use Resident load to make it done.

Regards

[Nikhil2725]

Im using DB script, How to use Resident Load.... I dont known about it..