Forum Discussion
Solved
Sequentially concatenating row values
Hi, I have a table that looks like this
| ACCOUNT | Attempt | CH_ATTEMPT_STATUS |
| 00000001 | 1 | S |
| 00000001 | 2 | N |
| 00000002 | 1 | S |
| 00000002 | 2 | N |
| 00000002 | 3 | N |
| 00000003 | 1 | F |
| 00000003 | 2 | F |
| 00000003 | 3 | S |
| 00000004 | 1 | N |
| 00000004 | 2 | S |
For each Account, I want to sequentially concatenate the row values in the 'CH_ATTEMPT_STATUS' column by ascending values from the 'Attempt' column. So going by the above example, the resulting column would look like this..
| ACCOUNT | Attempt | CH_ATTEMPT_STATUS | CH_Pat |
| 00000001 | 1 | S | S |
| 00000001 | 2 | N | SN |
| 00000002 | 1 | S | S |
| 00000002 | 2 | N | SN |
| 00000002 | 3 | N | SNN |
| 00000003 | 1 | F | F |
| 00000003 | 2 | F | FF |
| 00000003 | 3 | S | FFS |
| 00000004 | 1 | N | N |
| 00000004 | 2 | S | NS |
I don't even know where to start, how would I do this?
Thanks,
Dan
Hi, try with this calculated column
CH-PAT = CONCATENATEX(FILTER(Table1,Table1[ACCOUNT]=EARLIER(Table1[ACCOUNT]) && Table1[Attempt]<=EARLIER(Table1[Attempt])),Table1[CH_ATTEMPT_STATUS],,Table1[Attempt],ASC)
Regards
Victor
Lima - Peru
This Calculated Column will also get you the desired RESULTS :smileywink:
Column = CONCATENATEX ( TOPN ( TableName[Attempt], FILTER ( TableName, TableName[ACCOUNT] = EARLIER ( TableName[ACCOUNT] ) ) ), TableName[CH_ATTEMPT_STATUS],, TableName[Attempt] )
3 Replies
Hi, try with this calculated column
CH-PAT = CONCATENATEX(FILTER(Table1,Table1[ACCOUNT]=EARLIER(Table1[ACCOUNT]) && Table1[Attempt]<=EARLIER(Table1[Attempt])),Table1[CH_ATTEMPT_STATUS],,Table1[Attempt],ASC)
Regards
Victor
Lima - Peru
This Calculated Column will also get you the desired RESULTS :smileywink:
Column = CONCATENATEX ( TOPN ( TableName[Attempt], FILTER ( TableName, TableName[ACCOUNT] = EARLIER ( TableName[ACCOUNT] ) ) ), TableName[CH_ATTEMPT_STATUS],, TableName[Attempt] )Brilliant, thanks!
