Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the attached spreadsheet with the data that I would like to return.
milestone | activity | comment |
1 | painting the room | covered all the furniture |
1 | painting the room | taped off the windows |
1 | painting the room | primed the walls |
1 | painting the room | painted the walls |
I believe the correct solution is to create the script upon the load, but wasn't sure. The milestone is the same as well as the activity, but the update comments get appended at some frequency. My intention is to join based on the activity and then have a "grouped" comment variable/argument that would contain all the comments relating to that activity. This would in theory have a list box with one line with all the comments concatenated rather than currently they are separate items.
Do you desire to get something like this??
If yes then you can use the following script:
Table:
LOAD milestone,
activity,
comment
FROM
Community_156995.xlsx
(ooxml, embedded labels, table is Sheet1);
Join(Table)
LOAD activity,
Concat(comment, ', ') as [All Comments]
Resident Table
Group By activity;
HTH
Best,
S
Test:
Load *, RowNo() as ID Inline
[
milestone, activity, comment
1, painting the room, covered all the furniture
1, painting the room, taped off the windows
1, painting the room, primed the walls
1, painting the room, painted the walls
];
NoConcatenate
Final:
Load
milestone, activity, CONCAT(comment,',', ID) as comment
Resident Test
Group By milestone, activity;
Drop Table Test;
Do you desire to get something like this??
If yes then you can use the following script:
Table:
LOAD milestone,
activity,
comment
FROM
Community_156995.xlsx
(ooxml, embedded labels, table is Sheet1);
Join(Table)
LOAD activity,
Concat(comment, ', ') as [All Comments]
Resident Table
Group By activity;
HTH
Best,
S
this works exactly as I thought. However how can I change it when I am joining from the spreadsheet?
Test:
Load
RowNo() as ID,
milestone,
activity,
comment
From YourExcelFileName;
NoConcatenate
Final:
Load
milestone, activity, CONCAT(comment,',', ID) as comment
Resident Test
Group By milestone, activity;
Drop Table Test;
Getting closer...when I paste the above script it returns
Table not found
Join(Table)
Did you paste the whole script or just the partial script?
Table: (If this is your table name than you won't get the same error)
LOAD milestone,
activity,
comment
FROM
Community_156995.xlsx
(ooxml, embedded labels, table is Sheet1);
Join(Table)
LOAD activity,
Concat(comment, ', ') as [All Comments]
Resident Table
Group By activity;
HTH
Best,
S
Slight change in the script if you want the grouping to be by both the milestone and activity: PFA the application also.
Table:
LOAD milestone,
activity,
comment
FROM
Community_156995.xlsx
(ooxml, embedded labels, table is Sheet1);
Join(Table)
LOAD milestone,
activity,
Concat(comment, ', ') as [All Comments]
Resident Table
Group By activity, milestone;
HTH
Best,
S !
I didn't reflect the "table" name. Thanks again.
Not a problem. I am glad that I was able to help.
Best,
S