Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

deere2013
New Contributor III

conditional concat

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.

Tags (1)
1 Solution

Accepted Solutions

Re: conditional concat

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

9 Replies
MVP
MVP

Re: conditional concat

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;

Re: conditional concat

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

deere2013
New Contributor III

Re: conditional concat

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

MVP
MVP

Re: conditional concat

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;

deere2013
New Contributor III

Re: conditional concat

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

Table not found
Join(Table)

Re: conditional concat

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

Re: conditional concat

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 !

deere2013
New Contributor III

Re: conditional concat

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

Re: conditional concat

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

Best,

S