topic Re: conditional concat in QlikView

conditional concat

Anonymous — Fri, 20 Mar 2015 18:16:36 GMT

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.

Re: conditional concat

MK_QSL — Fri, 20 Mar 2015 18:33:53 GMT

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

sunny_talwar — Fri, 20 Mar 2015 18:38:58 GMT

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,

Re: conditional concat

Anonymous — Fri, 20 Mar 2015 18:43:46 GMT

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

Re: conditional concat

MK_QSL — Fri, 20 Mar 2015 18:47:01 GMT

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;

Re: conditional concat

Anonymous — Fri, 20 Mar 2015 19:02:54 GMT

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

Table not found
Join(Table)

Re: conditional concat

sunny_talwar — Fri, 20 Mar 2015 19:07:49 GMT

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,

Re: conditional concat

sunny_talwar — Fri, 20 Mar 2015 19:10:00 GMT

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 !

Re: conditional concat

Anonymous — Fri, 20 Mar 2015 19:25:52 GMT

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

Re: conditional concat

sunny_talwar — Fri, 20 Mar 2015 19:27:44 GMT

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

Best,