Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Nikhil2725
Creator II
Creator II

Comma separated values

Hi All,

I have table below:

user_idSubject_access_tracking_idsubject_idLender_offer_id
22725441154
215895611154
215895721154
215895831154
7708043
72002613
1214944
1215814
1216624
1216734
134015
134125
134235
134445

 

However, I want to display like below:

user_idLender_offer_idSelection_order
211544,1,2,3
734,1
1244,1,2,3
1351,2,3,4

 

Kindly help me in displaying.

 

 

1 Solution

Accepted Solutions
Shubham_Deshmukh
Specialist
Specialist

Try this :

myData:
LOAD user_id, 
     Subject_access_tracking_id, 
     subject_id, 
     Lender_offer_id
     FROM
yourDbTable;

residentData:
Load *,RowNo()
Resident myData;

drop table myData;

Read about resident load - it plays imp role in Qlik data modelling.

 

 

View solution in original post

12 Replies
Shubham_Deshmukh
Specialist
Specialist

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
Creator II
Creator II
Author

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
Specialist
Specialist

We have simpler way to do this,

Dimension : user_id

Exp1 : Lender_offer_id

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


sdsdds.png

Nikhil2725
Creator II
Creator II
Author

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
Specialist
Specialist

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
Specialist
Specialist

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
Creator II
Creator II
Author

Can u share the code snippets??
Shubham_Deshmukh
Specialist
Specialist

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);

 

sdsdds.png

 

 

 

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
Creator II
Creator II
Author

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