conditional concat

[Anonymous]

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.

[MK_QSL]

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;

[sunny_talwar · Accepted solution]

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

[Anonymous]

this works exactly as I thought.  However how can I change it when I am joining from the spreadsheet?

[MK_QSL]

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;

[Anonymous]

Getting closer...when I paste the above script it returns

Table not found
Join(Table)

[sunny_talwar]

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

[sunny_talwar]

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 !

[Anonymous]

I didn't reflect the "table" name.  Thanks again.

[sunny_talwar]

Not a problem. I am glad that I was able to help.

Best,

S